Sql中有关xml的一些操作

Sql中有关xml的一些操作

最近做的一个项目，数据是用xml格式来做保存的，sql操作xml本人之前还真没接触过，所以做得没那么顺，以下是对这段时间xml操作有用到过的一些记录，其实都是帮助文档的内容，只是做个备忘罢了。本人的遗忘曲线貌似斜率太大了……

1、Query('XQuery')对xml字段进行查询,结果还是xml，例如

declare @myDoc xml

set @myDoc = '<Root>

<ProductDescription ProductID="1" ProductName="Road Bike">

<Features>

  <Warranty>1 year parts and labor</Warranty>

  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

</Features>

</ProductDescription>

</Root>'

SELECT @myDoc.query('/Root/ProductDescription/Features')

结果为：

<Features>

  <Warranty>1 year parts and labor</Warranty>

  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

</Features>

 

2、For xml 把记录转化为xml格式，其后跟RAW、AUTO、EXPLICIT、PATH具体细节见帮助文档 例子

表记录  

语句 select ID,Name from TBTest for xml RAW

结果

<row ID="1">

  <Name>

    <Root>

      <ProductDescription ProductID="1" ProductName="Road Bike">

        <Features>

          <Warranty>1 year parts and labor</Warranty>

          <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

        </Features>

      </ProductDescription>

    </Root>

  </Name>

</row>

<row ID="2">

  <Name>

    <Root>

      <ProductDescription ProductID="1" ProductName="Road Bike">

        <Features id="1">

          <Warranty>1 year parts and labor</Warranty>

          <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

        </Features>

      </ProductDescription>

    </Root>

  </Name>

</row>

 

为生成的xml加个跟节点只要在语句后加个”,Root(‘RootName’)”，如select ID,Name from TBTest for xml RAW ，Root(“ThisIsRoot”) 的结果会把上页面的xml文件加上〈ThisIsRoot〉的跟节点，也可以把上面的那ID变为xml的一个节点而不是属性，只要在语句中加“，ELEMENTS”

如select ID,Name from TBTest for xml RAW ，Root(“ThisIsRoot”) ，ELEMENTS这样ID则成为和Name同一层次的节点，也就是

<row>

  <ID>1</ID>

  <Name>..</Name>

</row>

 

3、Openxml()把xml内容当作表来使用，详细内容见帮助文档下面是例子

DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc ='

<ROOT>

<Customer CustomerID="VINET" ContactName="Paul Henriot">

   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"

           OrderDate="1996-07-04T00:00:00">

      <OrderDetail ProductID="11" Quantity="12"/>

      <OrderDetail ProductID="42" Quantity="10"/>

   </Order>

   <Phone>654321</Phone>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

           OrderDate="1996-08-16T00:00:00">

      <OrderDetail ProductID="72" Quantity="3"/>

   </Order>

   <Phone>123456</Phone>

</Customer>

</ROOT>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- SELECT stmt using OPENXML rowset provider

SELECT *

FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)

         WITH (OrderID       int         '../@OrderID',

               CustomerID  varchar(10) '../@CustomerID',

               OrderDate   datetime    '../@OrderDate',

               ProdID      int         '@ProductID',

               Qty         int         '@Quantity',

               Phone varchar(20) '../../Phone')

结果

后面要是要加筛选条件就何正常查表一样，字段为with语句的字段

4、还有有关value()、exist()、modify()等见帮助文档