SQL XML query operations

SQL XML query operations

 

Query XML node value: it is specified by nodes to get the value of the node through the value attribute.
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)

Query the attributes of an XML node:
Declare   @ XML XML
Set   @ XML = ' <Employee> <employee ID = "1"/> <employee ID = "2"/> <employee ID = "3"/> </employee> '

Select Employeeid. Value ( ' . // @ ID ' , ' Nvarchar (500) ' ) As Reportcolumnid

From   @ XML . Nodes ( ' /Employee/employeeid ' )
X (employeeid)

You can use subqueries to query the values of multiple XML nodes:
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 query method:

Declare   @ IDOC   Int
Declare   @ Doc   Varchar ( 1000 )
Set   @ Doc   = '
<Root>
<Customer customerid = "Vinet" contactname = "Paul henriot"/>
</Root> '
-- -Create an internal document format
Exec Sp_xml_preparedocument @ IDOC Output, @ Doc

Select *
From Openxml ( @ IDOC , ' /Root/customer ' , 1 )
With (Customerid Varchar ( 10 ),
Contactname Varchar ( 20 ))

Openxml query method:

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> '
-- -Create an internal document format
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 ' )