Working with xml data type variable sql server 2008 asp.net c#

Posted by on August 8, 2010 Leave a comment (1) Go to comments

Introduction:
In this article,i am going to explain about how to use the new xml data type as variable, and how to
define the xml data.

Main:

The ‘xml’ datatype helps us to declare xml data in sql server 2008.

DECLARE @xmlData AS XML
SET @xmlData='
<Customers>
  <CustomerID>TELRK</CustomerID>
  <CompanyName>Telerik, Inc</CompanyName>
  <ContactName>Stephen Forte</ContactName>
  <ContactTitle>Sales Representative</ContactTitle>
  <Address>5–9 Union Square West</Address>
  <City>New York</City>
  <PostalCode>10028</PostalCode>
  <Country>USA</Country>
  <Phone>030-0074321</Phone>
  <Fax>030-0076545</Fax>
  </Customers>'
SELECT @xmlData

How to insert a xml data into table?

--Insert Static XML via a variable
DECLARE @xmlData AS XML
SET @xmlData = '
<Orders>
  <Order>
    <OrderID>5</OrderID>
    <CustomerID>65</CustomerID>
    <OrderAmount>25</OrderAmount>
  </Order>
</Orders>'
--insert into the table
INSERT INTO OrdersXML (OrderDocID, xOrders) Values (1, @xmlData)

You can insert XML into these columns in a variety of other ways: XML Bulk Load, loading from an XML
variable (as shown above),

How to use xml bulk load?

In SQL Server 2000, XML Bulk Load allowed users to load large XML documents on the client side.
XML Bulk Load works by reading the XML and producing SQL INSERT statements that run on the client
in a batched fashion. SQL Server 2005 and 2008 greatly enhance XML Bulk Load by allowing it to run
on the server and to load directly into an xml data type column.

Add a note hereUsing the enhanced XML Bulk Load requires using the system rowset provider function
OPENROWSET and specifying the BULK provider,

--create a table with an xml column
CREATE TABLE tblXmlCustomers
 (CustomerID int PRIMARY KEY IDENTITY,
 
  CustomerXML xml NOT NULL)
 
--this file will load 1 record in (SINGLE_CLOB); for more records use a format file)
INSERT INTO tblXmlCustomers)
 SELECT * FROM OPENROWSET(BULK 'C:\customer_01.xml', SINGLE_CLOB) AS XmlData)

Querying xml data using xquery?

XQuery provides a native and elegant way to query XML data.

DECLARE @XML xml
SET @XML='
<catalog>
  <book category="ITPro">
    <title>Windows Step By Step</title>
    <author>Bill Zack</author>
    <price>49.99</price>
  </book>
  <book category="Developer">
    <title>Developing ADO .NET</title>
    <author>Andrew Brust</author>
    <price>39.93</price>
  </book>
  <book category="ITPro">
    <title>Windows Cluster Server</title>
    <author>Stephen Forte</author>
    <price>59.99</price>
  </book>
</catalog>'
 
SELECT @XML.query('
   for $b in /catalog/book
   where $b/@category="ITPro"
   order by $b/author[1] descending
   return ($b)')

Conclusion:
Hope this helps,
Happy coding.