Lấy XML từ SQL Server 2000 Đôi khi tôi phải kéo dữ liệu từ cơ sở dữ liệu SQL Server của tôi vào một định dạng văn bản XML. Làm thế nào để làm điều đó với SQL Server 2000? Kỹ thuật Để thực hiện nhiệm vụ này
12.4 Retrieve XML from SQL Server 2000
Sometimes I have to pull data from my SQL Server database into an XML document
format. How do I do that with SQL Server 2000?
To accomplish this task, you will create a Command object with the Transact-SQL
SELECT statement that you want to execute. However, at the end of your SQL statement,
you will add the clause FOR XML mode. The mode can be any of these that are listed:
• RAW. With this mode, each of the rows that is returned in the query result is made
into a generic XML element with as the identifier tag.
• AUTO. Each of the rows and the columns are identified with tags for each of the
elements and attributes, using the column names as identifier tags.
• EXPLICIT. Here, you have to nest and create your query in a particular way. For
more information on this mode, check out the SQL Server Books Online.
For this example, the code will use the RAW mode and look like this:
SELECT * FROM Customers FOR XML RAW
To execute the SQL statement in this case, you use the method ExecuteXMLReader.
When you use this method, an XMLReader is returned. You should then iterate through
the elements as seen in How-To 12.2.
Open and run the Visual Basic .NET-Chapter 12 solution. From the main Web page,
click on the hyperlink with the caption How-To 12.4: Retrieving XML from SQL Server
2000. When the page loads, you will see an example of a T-SQL statement that retrieves
data from SQL Server 2000 in an XML format. Click on the button labeled Retrieve
XML, and the data will be listed in the TextArea at the bottom of the form (see Figure
1. Create a Web Form. Then place the Label, TextBox, and Button objects as seen in
Figure 12.4 on the form with the properties in Table 12.8 set.
Table 12.8. Label, TextBox, and Button Control Property Settings
Object Property Setting
Label Text SQL To Execute
TextBox ID txtSQLToExecute
Text SELECT * FROM Customers FOR XML AUTO,
Button ID btnRetrieveXML
Text Retrieve XML
TextArea ID taOutput
HyperLink ID hplReturnToMain
2. Add the code in Listing 12.10 to the Click event of btnRetrieveXML. Taking the
SQL statement displayed in the "Technique" section, the Command object
cmdCust is created, and ExecuteXMLReader is invoked. The XMLReader then
iterates through each of the elements in the document, and they concatenate to a
string. Last, the string is assigned to the InnerText property of taOutput, and the
connection to the XMLReader object is closed.
Listing 12.10 wfrmHowTo12_4.aspx.vb: Reading an XML Document Using
Private Sub btnRetrieveXML_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnRetrieveXML.Click
Dim cnn As New SqlClient.SqlConnection(BuildCnnStr("(local)",
Dim cmdCust As SqlClient.SqlCommand = New SqlClient.SqlCommand( _
Dim xrCust As System.Xml.XmlReader
Dim intAtts As Int32
Dim intCurrAtt As Int32
Dim strOut As String
xrCust = cmdCust.ExecuteXmlReader()
intAtts = xrCust.AttributeCount
If xrCust.NodeType System.Xml.XmlNodeType.XmlDeclaration
If intAtts > 0 Then
For intCurrAtt = 0 To intAtts - 1
strOut &= xrCust(intCurrAtt) & vbCrLf
strOut &= xrCust.Value & vbCrLf
Catch excp As Exception
strOut &= "Following Error Occurred: " & excp.Message
strOut &= vbCrLf & "Done Processing "
taOutput.InnerText = strOut
If Not xrCust Is Nothing Then
You have been using the BuildCnnStr() function throughout this
book. You should add this function to a module or copy it from
other chapters. Here is the code for the function:
Function BuildCnnStr(ByVal strServer As String,
ByVal strDatabase As String) As String
Dim strTemp As String
strTemp = "Data Source=" & strServer & ";"
strTemp &= "Initial Catalog=" & strDatabase & ";"
strTemp &= "Integrated Security=SSPI"
Figure 12.4. The information displayed here was read from SQL Server in an XML
Normally, you would be taking the XML document that the command object returned
and passing that on to another system that requires the data to be in XML format. The
data was displayed from the XMLReader for demonstration purposes.