Operate XML with T-SQL

Operating XML with T-SQL released on: 11/24/2004 | Updated on: 11/24/2004

Rick Dobson

In this article, Rick Dobson quickly and effectively explains how to use an XML template. When reading this article, you may also want to read or repeat what Rick posted in January 2004 about using the Web Service toolkit to create web services and their clientsArticleTom Moreau wrote a column on transferring XML to stored procedures in March 2001, and an article about "bringing XML back to power" published by Anton Britten in February 2002.

Have you noticed how many "Hot Topics" in the past few years need skill sets that are completely different from the common skills of typical SQL Server DBAs? For example, isn't it good if we can use existing skills (such as writing T-SQL) to return the query results as XML documents passed on the web? However, Microsoft may consider this when designing the functional set of XML template technology. This technology was initially attached to SQL Server 2000 and was slightly adjusted in subsequent web versions. With this technology, browser clients can call T-SQL statements that are embedded in an XML template in a virtual directory and display XML results. These templates can accept parameter values, and you can even allow clients to dynamically update databases. The XML syntax of these templates is very simple, and the T-SQL is undoubtedly familiar to everyone.

Because XML is text separated by tags, it is easy for the client to read XML in a web browser. For example, if your applicationProgramMore traditional formatted output is required. You can use XSLT (XSL conversion) to convert an XML result set to an HTML table or other layout. In this article, I will show you how to use this technology with SQLXML 3.0 SP1 for SQL Server 2000.[Compared with the optional for XML clause that can be used for Server programming, SQLXML technology allows the client to support SQL Server through programming. -Ed .]If you do not have the latest versionWww.msdn.com/sqlxmlDownload.

Quick review of XML templates

The XML template depends on three SQL Server 2000 functions. First, the template must reside in a special virtual IIS Directory pointing to the SQL Server database. Second, you must append a for XML clause to any T-SQL statement in the returned results. (This clause allows you to specify the result format and the location where the XML format of the result set occurs .) Third, you need to embed T-SQL statements into an XML document (XML template.

SQL Server 2000 was initially accompanied by the IIS virtual directory management client tool, but later web versions modified its functionality. To start the tool, Select Start | SQLXML 3.0 | configure IIS support. I used this tool to create a directory named sptemplates with a nested folder named mytemplates and a template type, and the sptemplates directory points to the sample pubs database. (You can specify any name for the virtual directory, but if you want to use an XML template, the directoryRequiredContains a template folder .) Unless you specify windows integrated authentication, all users must log on to the same SQL Server. However, you can use the traditional SQL server security settings to control the logon and Windows logon permissions.

Append the for XML clause to the end of multiple common T-SQL query statements. The three common parameters of a sub-statement include raw, auto, and nested. If you return a query result based on a single table, raw is enough. The returned XML document contains a Row Element for each row of the column value and displays it as a property value. For query results based on two or more tables and view-based queries, the auto and nested parameters are suitable. As you will see later in this article, any parameter can display nested results with its source tag.

The nested parameter is valid only when you specify the client format for the query result. When retrieving results from a heavily loaded SQL Server, the client format setting can improve scalability. When you specify the nested parameter in the client format, your query statement can contain the group by clause and Aggregate functions.NoIt can be used in raw and auto parameters.

In the template folder of the given virtual directory, you can specify multiple XML template queries and embed database maintenance statements, such as insert and delete. Each XML template query must have a unique root tag in its XML document, and the root tag must contain a reference to the urn: Schemas-Microsoft-com: XML-SQL namespace, this namespace defines special template elements, such as query, header, And Param. You use start and end tags to represent an element; T-SQL statements are embedded within the start and end query tags. You can specify the client format settings with the properties of the query tag, and if your T-SQL statement references parameters, you can declare them with the param tag embedded in the header tag.

Return query results

Listing 1Displays the content of the listauthors. xml file. This file is a template query that returns the names of all authors by last name from the authors table. Because my sptemplates virtual directory points to the pubs database, the query references the authors table in the database. In listing 1, the root tag specifies the namespace with the Element Definition queried using the XML template. (You can replace root with any unique root name .)

List 1. query the template of the selected column in The authors table.

 
<Root xmlns: SQL = "urn: Schemas-Microsoft-com: XML-SQL"> <SQL: Query> select au_fname, au_lname from authors order by au_lname for XML auto </SQL: Query> </root>
 

Figure 1Display the browser window containing the listauthors. XML query results. The address box displays the URL of template query in the mytemplates folder that calls the sptemplates virtual directory, which is located on the IIS server named cablat. Since the auto parameter is used in the for XML clause in Listing 1, the name of the table used as the query source is displayed in each row. If I use raw instead of Auto, the row will replace the author in each row of the result set.

In many applications, you need to let users fine-tune the content of the result set at runtime. parameters are a common method to implement this function.Listing 2The listsearchedauthors. XML template query in will allow users (hold your breath !) Specify the author's last name at runtime. If you do not specify a last name, the query returns a default author whose last name is Bennet. The param tag allows you to specify parameters for template query. All you need to do is to use the name attribute of the param tag to specify the name of a parameter and place an @ symbol in front of the name in the T-SQL for this query. The value between the start and end Param mark indicates the default parameter value.

List 2. Demonstrate template query for parameter syntax.

<Root xmlns: SQL = "urn: Schemas-Microsoft-com: XML-SQL"> <SQL: Header> <SQL: Param name = 'searchname'> Bennet </SQL: param> </SQL: Header> <SQL: Query> select au_fname, au_lname from authors where au_lname = @ searchname order by au_lname for XML auto </SQL: Query> </root>
 

Figure 2Displays the query results by calling the listsearchedauthors. XML template. You can specify a parameter value by adding a question mark (?) After the template query URL, and there is a pair of values separated by equal signs (=. The first item in the value pair is the parameter name, and the second item is the parameter value. Because there are two authors named ringer, The result set will contain two rows starting with the authors mark.

The next list shows a more complex template query named listtitlesforauthors. xml. The T-SQL statement in this query joins three tables to display the title of an author. By specifying the author's last name as a parameter, you can specify the authors they are interested in, and query again using Bennet as the default author.Listing 3Listtitlesforauthors. XML in shows the syntax used to implement these specifications and specify the client format settings. First, it specifies value 1 to the client-side-XML Attribute. Then, it specifies nested as the for XML parameter.

Listing 3. template query with parameters and client format settings.

<Root xmlns: SQL = "urn: Schemas-Microsoft-com: XML-SQL"> <SQL: Header> <SQL: Param name = 'searchname'> Bennet </SQL: param> </SQL: Header> <SQL: Query client-side-xml = "1"> select. au_fname,. au_lname, T. title from authors a join titleauthor Ta on. au_id = TA. au_id join titles t on TA. title_id = T. title_id where au_lname = @ searchname for XML nested </SQL: Query> </root>
 

Figure 3The result of listtitlesforauthors. xml called by the author whose last name is ringer is displayed. You can see that two ringer (without double meanings) have co-authored a book, but they have also written another book. The query results of the titles table are nested in each row of the authors table.

Query and update a database using a template

In addition to returning result sets as XML documents in a browser, XML template queries allow you to execute database maintenance tasks with T-SQL keywords. In list 4 , the inserttitleauthor. XML template queries a row in each of the three tables (titles, authors, and titleauthor. Note that this list uses the insert keyword to specify the selected column value for the new row. In addition, the sample Code shows how to use multiple parameters in a single template query. (A comment line marks the beginning of the parameter declaration .) Each parameter has a default value, but you can override these values when calling the template query. list 4. Insert a new row template query in the titles, authors, and titleauthor tables.

 
   
    
     
    
      123-45-6789 
     
    
      Rick 
     
    
      Dobson 
     
    
      pc1234 
     
    
      database development 
     
    
   
     insert into titles (title_id, title) values (@ in_title_id, @ in_title) insert into authors (au_id, au_fname, au_lname, contract) values (@ in_au_id, @ in_au_fname, @ in_au_lname, 1) insert titleauthor (au_id, title_id) values (@ in_au_id, @ in_title_id) 
    
   

It is common to have multiple parameters when inserting rows. The syntax for specifying multiple parameters is the same as that for specifying a single parameter, unless you use the and symbol (&) to separate the name-value pairs. The default settings of the inserttitleauthor. XML template query specify the author name and last name of Rick and Dobson. For example, you can enter the following content in the browser address box and rewrite these default settings with custom Rickie and DOBs values:

 
Http: // cablat/sptemplates/mytemplates/inserttitleauthor. xml? In_au_fname = Rickie & in_au_lname = DOBs
 

For code, it is easier to delete rows from a table. All you need to do is to specify one or more rows for which you want to apply the delete keyword, you can perform this operation by using the primary key value of the row as the parameter of the WHERE clause in any Delete statement.Listing 5The deletetitleauthor. XML template query in deletes a row from the titleauthor table. After the foreign key reference is deleted from the titleauthor table, this query also deletes a row from the authors and titles tables.

Listing 5: Used to delete a template query row from the titleauthor, authors, and titles tables.

<Root xmlns: SQL = "urn: Schemas-Microsoft-com: XML-SQL"> <SQL: Header> <SQL: param name = 'in _ au_id '> 123-45-6789 </SQL: param> <SQL: Param name = 'in _ title_id'> pc1234 </SQL: param> </SQL: Header> <SQL: query client-side-xml = "1"> Delete from titleauthor where au_id = '2017-45-6789 'and title_id = 'pc1234' Delete from authors where au_id = '2017-45-6789 'Delete from titles where title_id = 'pc1234' </SQL: query> </root>
 

Summary

XML template query is a simple and quick way to generate xml output in a browser. While you can populate the template with content other than the T-SQL, SQL Server experts appreciate the ability to generate xml using T-SQL technology. In addition to creating XML format content for the browser, you can also use template query to operate values in the SQL Server database. You now know how to use parameters to dynamically execute data access and data operation tasks at runtime. Try it!

Download 407rick. txt

LinkWww.msdn.com/sqlxml

Abstract: XML in SQL Server 2005

SeeHttp://msdn.microsoft.com/library/default.asp? Url =/library/en-US/dnsql90/html/sql2k5xml. aspOn "XML support in Microsoft SQL Server 2005 ".

•	XML is a native data type, which is selectively constrained by the XML architecture (in this case, this architecture is called typed XML. If you do not know the architecture or it is complex, untyped XML is applicable. XML columns can also be constrained by most T-SQL constraints (instead of unique keys, primary keys, or foreign keys.
•	The XML value (up to 2 GB/instance) is stored as blob internally.
•	The XML hierarchy can be up to 128 levels.
•	You can use a new DDL statement to create an index on a typed or untyped XML data column. The first index on the XML column (which requires the clustered index on the primary key column) is the B + tree index on all the tags, values, and paths of the XML instance in the primary index. Secondary XML indexes can be created as path, property, and value indexes.
•	Set showplan_xml on allows the XML showplan to be passed as a single-value result set.
•	The XML architecture is optional, but once defined (create XML Schema collection), the XML schema collection is stored as part of the system metadata, and its nodes are in unicode format.
•	There is a new built-in function: xml_schema_namespace ().
•	Xpathdocument replaces xmldocument for primary XML storage.
•	There is a CLR accessors for XML data.
•	A subset of XQuery is supported. XQuery-imagine flwor (for/let/where/orderby/return ).
•	There are five XML data types: Query (), value (), exist (), nodes (), and modify ().

Abstract: other resources

•	Using XML templates -_Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/xmlsql/ac_xml1_2cx1.asp
•	Executing template files using HTTP -_Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/xmlsql/ac_xml1 _114c. Asp
•	Enhancements to XML templates -_Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/sqlxml3/htm/otherimprovements_0ysz.asp
•	Comparing client-side XML formatting to server-side XML formatting -_Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/sqlxml3/htm/clientsidexml_8p4l.asp
•	Setting XML as a command using icommandstream and retrieving the results as an XML document (new information for SQL 2000 SP3 )-_Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/howtosql/ht_olehowt1 _2yd0. asp
•	Retrieving XML documents by using for XML on the client side -_Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/sqlxml3/htm/clientsidexml_0en3.asp
•	Passing parameters to templates -_Http://msdn.microsoft.com/library/en-us/xmlsql/ac_xml1_3h9v.asp
•	Executing SQL queries using templates (an nwind example )-_Http://msdn.microsoft.com/library/en-us/xmlsql/ac_xml1 _1c1f. Asp
•	820874 PRB: "Access Denied" error when you run the SQLXML template query in IIS
•	813731 how to: retrieve data from a SQL Server ce database and save the data in an XML document
•	307224 how to: use XML in connected and disconnected ADO. Net Applications
•	810784 PRB: error message "HTTP Error 404-file or directory not found" occurs when you access SQL Server 2000 with HTTP
•	News group-news. Microsoft. Public. sqlserver. xml and news. Microsoft. Beta. Whidbey. xml
•	SQLXML program manager's network diary -_Http://blogs.msdn.com/irwando

-Kw

For more information about SQL Server Professional and pinnacle Hing, visit its website http://www.pinpub.com/

Note: This is not the website of Microsoft Corporation. Microsoft is not liable for the content of the website.

Go to the original English page