You can return results as XML documents rather than a standard rowset. You can retrieve results using the FORXML clause in a SELECT statement. In your FORXML clause you specify an XML mode, RAW, AUTO, or EXPLICIT. Table 1 describes each XML mode.
Table 1. XML Modes Used with the FORXML Clause| XML Mode | Description |
|---|
| AUTO | Produces output with both element and attribute features in conjunction with a subquery. | | EXPLICIT | Transforms
the rowset that results from the query execution into an XML document.
Must be written in a specific way so that the additional information
about the required XML, such as expected nesting in the XML, is
explicitly specified as part of the query. | | RAW | Transforms
each row in the query result set into an XML element that has the
generic identifier <row>, or the optionally provided element name.
By default, each column value in the rowset that is not NULL is mapped
to an attribute of the <row> element. |
In Figure 1, we use the FORXML clause with AUTO mode to produce an XML document.
Figure 1. Creating a Sample XML Document with FORXML
--Create a Temp Table
DECLARE @XML_Table TABLE (customerID INT,customerName
VARCHAR(255),customerPhone VARCHAR(255))
--Insert some values to the table
INSERT INTO @XML_Table(
customerID,customerName,customerPhone)
VALUES
(1,'Tommy Liddle','555-7896')
INSERT INTO @XML_Table(
customerID,customerName,customerPhone)
VALUES
(2,'Steve Long','555-2436')
INSERT INTO @XML_Table(
customerID,customerName,customerPhone)
VALUES
(3,'Monica Banning','555-4287')
--Create our XML document
SELECT customerID,customerName,customerPhone
FROM @XML_Table
FOR XML AUTO
|
When
you execute the code, it returns the data in a data grid, which when
you click on the hyperlink, SQL Server Management Studio brings up the
XML document in another tab as a well-formed XML document.
Transforming XML Data
The introduction of the
XML data type in SQL Server 2005 and the enhanced features that SQL
Server 2008 has to offer make transforming XML data easier than ever.
For example, if you want to find the price and quantity for all
inventory items, you can use the example in Figure 2.
Figure 2. Using the OPENXML Clause
DECLARE @XMLData XML
DECLARE @Pointer INT
SET @XMLData =
'<INVENTORY>
<ITEM>
<Description>Baseball Bats</Description>
<Price>55.00</Price>
<Quantity>3</Quantity>
</ITEM>
<ITEM>
<Description>Baseball Gloves</Description>
<Price>25.00</Price>
<Quantity>10</Quantity>
</ITEM>
<ITEM>
<Description>Baseball Hats</Description>
<Price>15.00</Price>
<Quantity>7</Quantity>
</ITEM>
</INVENTORY>'
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@XMLData
SELECT Description,Price,Quantity
FROM OPENXML(@Pointer,'/INVENTORY/ITEM',2)
WITH (Description VARCHAR(255), Price money, Quantity INTEGER)
EXEC sp_xml_removedocument @Pointer
|
|
XML
must have a starting and ending tag. For example, <ITEM> is the
start-tag and </ITEM> is the ending tag. Also, XML must be HTML
encoded. That means XML tags must have < and >.
|
In the first part of our query, Figure 3,
we create two variables, @XMLData and @Pointer. The first variable is
used to store our XML data in an XML data type, and our second variable
is used as a pointer to our XML data. Then we populate our XML variable,
@XMLData, with XML data using the SET command.
Figure 3. Sample XML
DECLARE @XMLData XML
DECLARE @Pointer INT
SET @XMLData =
'<INVENTORY>
<ITEM>
<Description>Baseball Bats</Description>
<Price>55.00</Price>
<Quantity>3</Quantity>
</ITEM>
<ITEM>
<Description>Baseball Gloves</Description>
<Price>25.00</Price>
<Quantity>10</Quantity>
</ITEM>
<ITEM>
<Description>Baseball Hats</Description>
<Price>15.00</Price>
<Quantity>7</Quantity>
</ITEM>
</INVENTORY>'
|
In Figure 4, we use a system stored procedure to prepare our XML document by parsing the text using the XML parser.
Figure 4. Using sp_xml_preparedocument
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@XMLData
|
Next, we construct our SELECT statement to provide a tabular view of the XML data in Figure 12.3. Here, in Figure 12.5,
we use OPENXML, which provides an easy way to use an XML document as a
data source. OPENXML allows data in an XML document to be treated just
like the columns and rows of your data set.
Figure 5. SELECT with OPENXML
SELECT Description,Price,Quantity
FROM OPENXML(@Pointer,'/INVENTORY/ITEM',2)
WITH (Description VARCHAR(255), Price money, Quantity INTEGER)
|
Notice, in Figure 5, we use the WITH clause in our SELECT statement. The WITH clause provides a table format using either SchemaDeclaration or specifying and existing tablename.
Lastly, in Figure 6, we will remove the XML document we prepared. When you execute the entire script, it will produce the results shown in Figure 7.
Figure 6. sp_xml_removedocument
EXEC sp_xml_removedocument @Pointer
|
Figure 7. Results
Description Price Quantity
--------------- --------------------- -----------
Baseball Bats 55.00 3
Baseball Gloves 25.00 4
Baseball Hats 15.00 7
|
Keep in mind that XML
documents are not all the same. An XML document can also contain
attributes that will also need to be queried. For example, in Figure 8,
you see that we have the same data as in the previous example but in
attributes like Description, Price, and Quantity. They are not separate
tags.
Figure 8. Sample XML
DECLARE @XMLData XML
DECLARE @Pointer INT
SET @XMLData =
'<INVENTORY>
<ITEM Description="Baseball Bats" Price="55.00" Quantity="3" />
<ITEM Description="Baseball Gloves" Price="25.00" Quantity="4" />
<ITEM Description="Baseball Hats" Price="15.00" Quantity="7" />
</INVENTORY>'
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@XMLData
SELECT Description,Price,Quantity
FROM OPENXML(@Pointer,'/INVENTORY/ITEM',2)
WITH (Description VARCHAR(255) '@Description', Price MONEY '@Price', Quantity
INTEGER '@Quantity')
EXEC sp_xml_removedocument @Pointer
|
To make our query work, you will see that we made a minor change in our WITH clause in our SELECT statement. You have to specify the attribute for that column in the WITH clause (see Figure 9).
Figure 9. Change the Structure Inside the WITH clause
WITH (Description VARCHAR(255) '@Description', Price MONEY '@Price',
Quantity INTEGER '@Quantity')
|
|
