Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 21, 2009

Explore XML Data Type in SQL Server

By Yan Pan

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]
GO

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>'

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.ID

GO

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.

» See All Articles by Columnist Yan Pan



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM