using xml in sql server(转)

http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html

Using XML data has many applications. In databases in particular it can be used for passing parameters from client applications, exchange data between SQL modules, or storing details in XML columns. SQL Server 2005 offers new capabilities and native support for XML. Below is one example of utilizing some of the new XML methods in XML to shred XML data to table format.

-- Declare XML variable

DECLARE @data XML;

 

-- Element-centered XML

SET @data =

N'<data>

    <customer>

      <id>1</id>

      <name>Allied Industries</name>

    </customer>

    <customer>

      <id>2</id>

      <name>Trades International</name>

    </customer>

  </data>';

 

-- Using the query() method

SELECT T.customer.query('id').value('.', 'INT') AS customer_id,

       T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name

FROM @data.nodes('data/customer') AS T(customer);

 

-- Using the value() method

SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,

       T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name

FROM @data.nodes('data/customer') AS T(customer);

 

-- Select only customer which id equals 2 using the exist() method

SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,

       T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name

FROM @data.nodes('data/customer') AS T(customer)

WHERE T.customer.exist('id/text()[. = "2"]') = 1;

 

-- Attribute-centered XML

SET @data =

N'<data>

    <customer id="1" name="Allied Industries"/>

    <customer id="2" name="Trades International"/>

  </data>';

 

-- Using the value() method

SELECT T.customer.value('@id', 'INT') AS customer_id,

       T.customer.value('@name', 'VARCHAR(20)') AS customer_name

FROM @data.nodes('data/customer') AS T(customer);

 

 

-- Results

customer_id customer_name

----------- --------------------

1           Allied Industries

2           Trades International

Testing the processing for element-centered XML versus attribute-centered XML does show that attribute-centered XML is processed faster.

Resources:

XML Support in Microsoft SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms345117.aspx