服务器端生成xml的方法

1) 方法一：利用XmlReader，以流的方式生成

Public Function GetArchives() As String

Dim cSql As String

Dim Constr_ZT As String = Application("ConStr") & "zt" & Session("LOGZTID") & "sysinfo"

Dim Cycn As New SqlClient.SqlConnection(Constr_ZT)

Dim Cycmd As SqlClient.SqlCommand = New SqlClient.SqlCommand

Dim CydSet As DataSet = New DataSet

Dim Cyadp As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(cSql, Cycn)

Dim Cysb As System.Text.StringBuilder = New System.Text.StringBuilder

Try

Cycn.Open()

Cycmd.Connection = Cycn

Cysb = New System.Text.StringBuilder

Cyadp.SelectCommand.Connection = Cycn

'客户档案

cSql = "SELECT * FROM customer_list ORDER BY frem"

Cyadp.SelectCommand.CommandText = cSql

Cyadp.Fill(CydSet, "cus")

'供应商

cSql = "SELECT * FROM vendor_list ORDER BY frem"

Cyadp.SelectCommand.CommandText = cSql

Cyadp.Fill(CydSet, "pro")

'为各表字段设置属性类型，如不设置将以节点的形式返回

Dim i, Ci As Integer

For i = 0 To CydSet.Tables.Count - 1 Step 1

For Ci = 0 To CydSet.Tables(i).Columns.Count - 1 Step 1

CydSet.Tables(i).Columns(CydSet.Tables(i).Columns(Ci).ColumnName).ColumnMapping = MappingType.Attribute

'生成xml数据

Cysb.Append(Replace(CydSet.GetXml, "NewDataSet", "root"))

CydSet.Reset()

Return Cysb.ToString()

Catch ex As Exception

If Cycn.State = ConnectionState.Open Then Cycn.Close()

Return ""

End Try

End Function

例：

For XML 的

SQL 语句表达式：

SELECT…

FROM…

WHERE…

ORDER BY…

FOR XML (raw | auto [, ELEMENTS] |

explicit)

[, XMLData]

[, BINARY base64])

“for xml raw”与“for xml auto”的区别：

n for xml raw：

¨ 在结果集中每一行有一个元素

¨ 没有嵌套的子元素

¨ 在结果集中的列/值表示成元素的特性/值

¨ 和数据库的结果集很类似，但表示成XML 格式，很容易转换

n for xml auto

¨ 数据库中的表/视图名表示成元素名

¨ 列名用来表示特性，如果加ELEMENTS 选项，列表示成子元素

¨ 支持嵌套的 XML 输出结果，结果集中的层次结构（元素的嵌套）基于 SELECT 子句中指定的列所标识的表顺序

¨ 可以用表和列别名来改变元素和特性名

2) 方法二：利用DataSet，以块的方式生成

Public Function A() As String

Dim cSql As String

Dim Constr_ZT As String = Application("CONSTR") & "zt" & Session("LOGZTID") & "sysinfo"

Dim Cycn As New SqlClient.SqlConnection(Constr_ZT)

Dim Cycmd As New SqlClient.SqlCommand("", Cycn)

Try

Cycn.Open()

Cycmd.Connection = Cycn

Dim Cyreader As System.Xml.XmlReader = Cycmd.ExecuteXmlReader()

Dim Cysb As System.Text.StringBuilder = New System.Text.StringBuilder

Cysb.Append("")

cSql = "select * from customer_list order by frem for xml raw"

Cycmd.CommandText = cSql

Cyreader.Read()

While Not Cyreader1.EOF

Cysb.Append(Cyreader.ReadOuterXml())

End While

Cyreader.Close()

Cycn.Close()

cSql = "select * from vendor_list order by frem for xml raw"