服务器端生成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
Next
Next
'生成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
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
Next
Next
'生成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"
Cycmd.CommandText = cSql
Cyreader.Read()
While Not Cyreader1.EOF
Cysb.Append(Cyreader.ReadOuterXml())
End While
Cyreader.Close()
Cycn.Close()
Cysb.Append("")
Return Cysb.ToString() ''形成XML数据岛
Catch ex As Exception
If Cycn.State = ConnectionState.Open Then Cycn.Close()
Return " "
End Try
End Function
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"
Cycmd.CommandText = cSql
Cyreader.Read()
While Not Cyreader1.EOF
Cysb.Append(Cyreader.ReadOuterXml())
End While
Cyreader.Close()
Cycn.Close()
Cysb.Append("")
Return Cysb.ToString() ''形成XML数据岛
Catch ex As Exception
If Cycn.State = ConnectionState.Open Then Cycn.Close()
Return "
End Try
End Function
3) 方法一与方法二的比较
n 方法一适合小批量的数据查询,当数据量较大的时候,方法二的速度会快些。
n 对 web 服务器资源要求:方法一低,方法二高。