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?
Technique
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 <row /> 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.
Steps
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
12.4).
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.

Dim strOut As String

Try
cnn.Open()
xrCust = cmdCust.ExecuteXmlReader()

While xrCust.Read()

intAtts = xrCust.AttributeCount
If xrCust.NodeType <> System.Xml.XmlNodeType.XmlDeclaration
Then
If intAtts > 0 Then
For intCurrAtt = 0 To intAtts - 1
strOut &= xrCust(intCurrAtt) & vbCrLf
Next
Else
strOut &= xrCust.Value & vbCrLf
End If
End If
End While

Catch excp As Exception

strOut &= "Following Error Occurred: " & excp.Message

Finally

strOut &= vbCrLf & "Done Processing "
taOutput.InnerText = strOut