Using XML with SQL Server 2000

The Transact-SQL (T-SQL) query language for Microsoft SQL Server 2000 enables you to use special SQL syntax to return XML data directly from your database queries rather than the more standard row and column resultset. This is done by using the System.Data.SqlCommand.ExecuteXmlReader method that was mentioned in Chapter 5. Chapter 5 covered the SqlCommand object thoroughly, so in this section we will concentrate on the new syntax options for T-SQL queries that return XML data directly.

Note 

Please review Chapter 5 if you need more information on ADO.NET and the SqlCommand object. This section also discusses how to send XML data to SQL Server 2000.

In this section, you will look at T-SQL FOR XML queries and how to update SQL Server tables with XML.

Retrieving XML Data from T-SQL Queries

Returning XML data instead of a traditional database resultset is easy. All you need to do is to add a FOR XML clause to the end of your standard SQL query. There are also a few optional modifiers and options that enable you to vary the format of the XML output that is produced.

Here’s a standard SQL query that returns a database resultset:

SELECT * FROM jobs

Add the FOR XML clause with one of the three modifiers—RAW, AUTO, or EXPLICIT—to return XML data.

The SQL query uses the RAW modifier and produces the format of XML shown. Here is an example:

SELECT * FROM jobs FOR XML RAW <row job_ job_desc="New Hire - Job not specified"      min_lvl="10" max_lvl="50"/> <row job_ job_desc="Chief Executive Officer"      min_lvl="200" max_lvl="225"/>

Each row of data is returned as a <row> element with a set of attributes. The attribute names match the database column names, and the attribute values represent the data.

Using the AUTO modifier produces an XML format in which each row is returned as an element with a tag name that matches the table name. Here is an example:

SELECT * FROM jobs FOR XML AUTO <jobs job_ job_desc="New Hire - Job not specified"       min_lvl="10" max_lvl="50"/> <jobs job_ job_desc="Chief Executive Officer"       min_lvl="200" max_lvl="225"/>

The EXPLICIT modifier is used when you are constructing a query that must retrieve data from multiple tables.

The other optional parameters that can be added to the query are XMLDATA, which is used to include an in-line schema in your output; ELEMENTS, which is used to produce an XML format with nested elements instead of all data being held as attribute values; and BINARY BASE64, which is used if you need to include BLOB data in your output.

Here’s an example of a query that uses the ELEMENTS modifier, and the output:

SELECT * FROM jobs FOR XML AUTO, ELEMENTS <jobs> <job_id>1</job_id> <job_desc>New Hire - Job not specified</job_desc> <min_lvl>10</min_lvl> <max_lvl>50</max_lvl> </jobs> 

Keep in mind that XML data produced by SQL Server 2000 does not have a root element, so it is considered an XML fragment, not a complete, well-formed, XML document.

Now that you understand how to write XML queries, you can use them with an ADO.NET SqlCommand to return XML data to your application. Listing 7.15 gives an example of this.

Listing 7.15: Using the SqlCommand.ExecuteXMLReader Method

start example
Private Sub GetXMLData()    Dim myConn As SqlConnection = New SqlConnection()    myConn.ConnectionString = _        "Data Source=localhost; Initial " & _       "Catalog=pubs; Integrated Security=SSPI;"    myConn.Open()    Dim sqlString As String = _        "SELECT * from jobs FOR XML AUTO"    Dim myXMLCommand As SqlCommand = _       New SqlCommand(sqlString, myConn)    myXMLCommand.CommandType = CommandType.Text    Dim myXmlReader As XmlReader    myXmlReader = myXMLCommand.ExecuteXmlReader()    While myXmlReader.Read()        'process the XML data    End While    myXmlReader.Close()    myConn.Close() End Sub
end example

Next you will learn how to take XML data and use it to update SQL Server 2000 tables.

Updating SQL Server Tables with XML

In order to send XML directly to SQL Server 2000, you must use stored procedures. First you will call a system stored procedure that parses the XML data and loads it into memory:

sp_xml_preparedocument @document 

Then your stored procedure will use a SQL INSERT, UPDATE, or DELETE statement in conjunction with the special OPENXML clause to direct the XML elements and attributes into the appropriate tables and columns. This example selects job_id, job_desc, min_lvl, and max_lvl from each record in the XML data file and inserts it into the jobs table:

INSERT jobs SELECT * FROM OPENXML (@document, 'job', 1) WITH (job_id, job_desc, min_lvl, maxn_lvl)

This procedure is then completed by calling another system stored procedure to release the memory that is being used by the XML data file:

sp_xml_removedocument @document

The OPENXML queries can become quite complex when the data in an XML file must be separated into several different tables in the database.

Exercise 7.9 demonstrates how to use the ExecuteXmlReader method of the SqlCommand class.

Exercise 7.9: Using SqlCommand.ExecuteXmlReader

start example
  1. Start Visual Studio .NET and create a new Windows Application project called SQL-XML-Example. Rename the default form to frmQuery.

  2. Add a TextBox control and name it txtDisplay. Set the Multiline property to True and the ScrollBars property to Both. Your form should look like the following one.

    click to expand

  3. Add Imports statements to the top of the form’s code module:

    Imports System.Xml Imports System.Data.SqlClient
  4. In the frmQuery_Load event procedure, add code to create a SqlConnection and open the connection:

    Dim myConn As SqlConnection = New SqlConnection() myConn.ConnectionString = _     "Data Source=localhost; Initial " & _    "Catalog=pubs; Integrated Security=SSPI;"    myConn.Open()
  5. Set up the SqlCommand object, declare the XmlReader, and call the ExecuteXmlReader method:

    Dim sqlString As String = "SELECT * from jobs FOR XML AUTO" Dim myXMLCommand As SqlCommand = New SqlCommand(sqlString, myConn) myXMLCommand.CommandType = CommandType.Text Dim myXmlReader As XmlReader myXmlReader = myXMLCommand.ExecuteXmlReader()

  6. Declare a string variable to hold the output and set up a loop to read through the data in the XmlReader:

    Dim str As String While myXmlReader.Read()    Select Case myXmlReader.NodeType       Case XmlNodeType.Element          str &= "<" & myXmlReader.Name          While myXmlReader.MoveToNextAttribute()             str &= " " & myXmlReader.Name & "='" & _                myXmlReader.Value & "'"          End While          str &= "/>" & Environment.NewLine    End Select End While
  7. Display the output and close the XmlReader and the SqlConnection:

    txtDisplay.Text = str myXmlReader.Close() myConn.Close() 
  8. Save and test your work. The running application should look like this:

    click to expand

  9. Change the SQL statement in your code to use the RAW modifier:

    SELECT * from jobs FOR XML RAW 
  10. Test the application again and observe how the output has changed.

    click to expand

end example

start sidebar
Real World Scenario—XML for Application Integration

You are a software developer for a company that still runs most of its daily transaction processing on legacy mainframe applications. The day-to-day business operations are handled reliably by these applications, and your company has no plans to replace any of the applications in the foreseeable future. However, the applications provide only a few basic reports and have no easy interface to access the proprietary data storage format to create new reports. Your business manager and marketing department frequently request that you provide them with more detailed information than the legacy apps make available. Two maintenance programmers are responsible for making sure that the legacy mainframe applications keep running and for fixing any problems that occur. They have no time to code additional reports.

The only way that they will provide data to you is in the form of Comma Separated Value (CSV) text files. You find these files tedious to work with. Anytime there is a change in either your application or the legacy application, the CSV files have to be changed to accommodate the changes. Then you have to do extensive testing of even the smallest changes to either system, because even a small mistake when parsing those CSV files will make resulting reports incorrect.

A colleague has suggested that you should request that the mainframe team provide data in XML format. When you first mentioned this to the mainframe team, they expressed the opinion that “XML is just the latest silver bullet technology that has gotten too much hype.” After doing a little research, you gave a presentation explaining how simple the XML format is, and they agreed that it would not be too difficult for them to meet your request. After a couple more meetings, you were able to agree on a schema that they would follow to produce their output.

Now that you have the data in XML format, your job is much easier. You quickly learned to use the new .NET Framework classes to work with XML data just as if you were working with a database table. Other tools, such as XSLT, took a little longer to learn, but now you can quickly produce different formats from the data files and post them to the company intranet for direct access by management. Best of all, you discovered that the latest version of Microsoft Excel can load XML data files directly into a spreadsheet. Marketing analysts can use this data on their own without requiring you to do any coding at all.

A simple change in data exchange format has reduced the turnaround time for new data report requests by several weeks. That should look good on your next annual review.

end sidebar



MCAD/MCSD(c) Visual Basic. NET XML Web Services and Server Components Study Guide
MCAD/MCSD: Visual Basic .NET XML Web Services and Server Components Study Guide
ISBN: 0782141935
EAN: 2147483647
Year: 2005
Pages: 153

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net