In SQL Server 2000, you could return XML from SQL Server with the FOR
XML clause or parse XML into a result set with the OPENXML function. However, XML
was treated as a string and no native support was provided. To parse an XML string,
you need to get a handle with sp_xml_preparedocument to access the internal
document representation of the XML string, and then use sp_xml_removedocument
to remove the internal document and invalidate the document handle. It was
inconvenient. Therefore, most of the time, application developers ended up
writing code to handle XML strings with Microsoft XML Core Services (MSXML) and
its API like the IXMLDOMDocument interface. SQL Server 2005 and 2008 provide a
native XML data type that can be untyped or typed according to a collection of XML
schemas. For example, we create an XML schema below that describes products.
CREATE XML SCHEMA COLLECTION scProduct AS
N'<?xml version=”1.0″ encoding=”UTF-16″?>
<xsd:schema xmlns_xsd=”http://www.w3.org/2001/XMLSchema”><xsd:complexType name=”ProductType”>
<xsd:attribute name=”ID” type=”xsd:integer” use=”required” />
<xsd:attribute name=”Category” type=”xsd:string” use=”optional” />
<xsd:attribute name=”Price” type=”xsd:decimal” use=”optional” />
<xsd:attribute name=”Description” type=”xsd:string”
use=”optional” />
</xsd:complexType><xsd:element name=”root”>
<xsd:complexType>
<xsd:sequence>
<xsd:element name=”Product” type=”ProductType” minOccurs=”0″
maxOccurs=”unbounded” />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>’
GO
Then create a typed XML variable @product and associate it with the XML schema.
DECLARE @product xml(scProduct)SET @product='<root>
<Product ID=”1″ Category=”Outdoor Living” Price=”140″
Description=”Quick-Clamp Canopy” />
<Product ID=”2″ Category=”Outdoor Living” Price=”170″
Description=”Embossed Aluminum Solar Fountain” />
<Product ID=”3″ Category=”Outdoor Living” Price=”90″
Description=”Self-Watering Planters” />
<Product ID=”4″ Category=”Outdoor Living” Price=”400″
Description=”Teak Recliner And Picnic Table” />
<Product ID=”5″ Category=”Home” Price=”1500″
Description=”Leather Sleeper Ottoman” />
<Product ID=”6″ Category=”Home” Price=”300″
Description=”Media Cabinet” />
<Product ID=”7″ Category=”Home” Price=”120″
Description=”Insulated Curtains” />
<Product ID=”8″ Category=”Home” Price=”100″
Description=”Log Cabin Quilt And Bed Skirt” />
<Product ID=”9″ Category=”Home” Price=”30″
Description=”Throw Pillow” />
<Product ID=”10″ Category=”Home” Price=”50″
Description=”Bar Stool” />
</root>’
By
associating @product with a schema, SQL Server validates @product when it is
assigned a value or modified. The attribute values will also be checked. If you
try to change the first product to:
<Product ID="1" Category="Outdoor Living" Price="low" Description="Quick-Clamp Canopy"/>
You
would get this error message because “low” is not a valid integer value.
Msg 6926, Level 16, State 1, Line 3
XML Validation: Invalid simple type value: ‘low’. Location: /*:root[1]/*:Product[1]/@*:Price
The data stored in
@product is actually structured as in a relational table. We can create a table
with the same structure, and convert @product into relational data to populate
the table. We use two of the five XML data type methods provided in SQL Server
2005 and 2008. The nodes() method shreds XML into multiple rows, and the
value() method retrieves values of SQL type from the XML instance in each row
with XQuery.
CREATE TABLE [dbo].[Product](
[ID] [int] NOT NULL,
[Category] [varchar](20) NULL,
[Price] [decimal](8, 2) NULL,
[Description] [varchar](1000) NULL
) ON [PRIMARY]
GODECLARE @product xml(scProduct)
SET @product='<root>
<Product ID=”1″ Category=”Outdoor Living” Price=”140″ Description=”Quick-Clamp Canopy” />
<Product ID=”2″ Category=”Outdoor Living” Price=”170″ Description=”Embossed Aluminum Solar Fountain” />
<Product ID=”3″ Category=”Outdoor Living” Price=”90″ Description=”Self-Watering Planters” />
<Product ID=”4″ Category=”Outdoor Living” Price=”400″ Description=”Teak Recliner And Picnic Table” />
<Product ID=”5″ Category=”Home” Price=”1500″ Description=”Leather Sleeper Ottoman” />
<Product ID=”6″ Category=”Home” Price=”300″ Description=”Media Cabinet” />
<Product ID=”7″ Category=”Home” Price=”120″ Description=”Insulated Curtains” />
<Product ID=”8″ Category=”Home” Price=”100″ Description=”Log Cabin Quilt And Bed Skirt” />
<Product ID=”9″ Category=”Home” Price=”30″ Description=”Throw Pillow” />
<Product ID=”10″ Category=”Home” Price=”50″ Description=”Bar Stool” />
</root>’INSERT [dbo].[Product]
( [ID]
,[Category]
,[Price]
,[Description]
)
SELECT
T.c.value(‘(@ID)[1]’, ‘int’) AS ID,
T.c.value(‘(@Category)[1]’, ‘varchar(20)’) AS Category,
T.c.value(‘(@Price)[1]’, ‘decimal(8,2)’) AS Price,
T.c.value(‘(@Description)[1]’, ‘varchar(1000)’) AS Description
FROM @product.nodes(‘/root/Product’) T(c)
GO
XML can be very useful
when you need to update multiple rows in a database table based on end-user
input. Say if you have a product catalog application that displays all your
company’s products in a data grid. Every day, the marketing staffs research the
market and update the catalog. They can modify any attribute of any product.
However, when they click on the Save button, the changes they make need to be
passed to SQL Server and persisted in there. You could design your application
to pass updates to SQL Server whenever a staff member makes changes to a
product. However, SQL Server performs best with set-based operations. Updating
row by row is slower than updating multiple rows in one shoot. In our example, say
if the marketing staff wants to increase the price of product ID 1, 5, 7, 8 by
10%, they would select the items on the data grid, specify the increase
percentage, and then click on the Save button. How do you pass the list of IDs
as a table to SQL Server? The easiest way is to pass the IDs in an instance of XML
data type, and then use nodes() and value() method to convert the XML instance
to a table and join the table with the Product table. That way, the updates are
set-based.
DECLARE @IDs xml(scProduct)
DECLARE @percent decimal(2,2)SET @percent=0.1
SET @IDs='<root>
<Product ID=”1″ />
<Product ID=”5″ />
<Product ID=”7″ />
<Product ID=”8″ />
</root>’UPDATE p
SET p.Price = p.Price * (1+ @percent)
FROM [dbo].[Product] p
JOIN (
SELECT T.c.value(‘(@ID)[1]’, ‘int’) AS ID
FROM @IDs.nodes(‘/root/Product’) T(c)
) I
on p.ID=I.IDGO
Better yet, if the
staff wants to change the description of item 1 and 9, you can simply change
the XML instance passed to the UPDATE statement, and update the description
column.
DECLARE @Description xml(scProduct)SET @Description='<root>
<Product ID=”1″ Description=”Quick-Clamp Canopy – Brown” />
<Product ID=”9″ Description=”Chocolate Flowers on Yellow Accent Pillow” />
</root>’UPDATE p
SET p.Description = d.Description
FROM [dbo].[Product] p
JOIN (
SELECT T.c.value(‘(@ID)[1]’, ‘int’) AS ID, T.c.value(‘(@Description)[1]’, ‘varchar(1000)’) AS Description
FROM @Description.nodes(‘/root/Product’) T(c)
) d
on p.ID=d.ID
GO
Conclusion
Beginning with SQL
Server 2005, SQL Server provides extensive support
for XML data processing. XML is now a
native data type and methods like nodes() and values() can be used to convert XML into a relational table. The easy conversion
from XML to a relational table provides a way
for set-based updates based on user input.
