SQL XML类型的查询

http://www.cnblogs.com/stevenshi/archive/2009/11/26/1611458.html

 

查询XML节点 value：通过nodes 指定到节点通过Value属性取出值
Declare   @Xml  xml
set   @Xml = ' <Employee><ID>1</ID><ID>2</ID></Employee> '

SELECT   ID.value( ' . ' , ' Nvarchar(500) ' )  as  EmployeeID
     FROM   @Xml .nodes( ' Employee/ID ' ) Employee(ID)            
    
查询 XML节点的属性：            
Declare   @Xml  xml
set   @Xml = ' <Employee><EmployeeID ID="1" /><EmployeeID ID="2" /><EmployeeID ID="3" /></Employee> '
                
SELECT   EmployeeID.value( ' ./@ID ' , ' Nvarchar(500) ' )  as  ReportColumnID
                
                 FROM   @Xml .nodes( ' /Employee/EmployeeID ' ) 
                X(EmployeeID)

查询XML 多节点的值，可以通过子查询实现：
Declare   @Xml  xml
Set   @Xml   =   '
<X>
  <T><ID>1</ID><NAME>A1</NAME></T>
  <T><ID>2</ID><NAME>B2</NAME></T>
  <T><ID>3</ID><NAME>C3</NAME></T>
</X> '
SELECT   ID.value( ' . ' ,  ' NVARCHAR(100) ' )  As  ID,NAME.value( ' . ' , ' NVARCHAR(100) ' )  As  NAME
FROM  
(
     Select   
        T.C.query( ' ID ' )  As  ID, 
        T.C.query( ' NAME ' )  As  NAME
     From  
         @Xml .nodes( ' /X/T ' )  As  T(C)
)BT



OPENXML 查询方式：

DECLARE   @idoc   int
DECLARE   @doc   varchar ( 1000 )
SET   @doc   = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot" />
</ROOT> '
-- -创建文档内部格式
EXEC  sp_xml_preparedocument  @idoc  OUTPUT,  @doc

SELECT      *
FROM        OPENXML ( @idoc ,  ' /ROOT/Customer ' , 1 )
             WITH  (CustomerID   varchar ( 10 ),
                  ContactName  varchar ( 20 ))


OPENXML 查询方式：

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>
</Customer>
</ROOT> '
-- -创建文档内部格式
EXEC  sp_xml_preparedocument  @idoc  OUTPUT,  @doc

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'