SELECT @ProductBillingXML.query('/ProductBilling/OrderItems/Item') : XML query « XML « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. XML
3. XML query

4>
5>
6> CREATE TABLE dbo.ProductBilling
7> (ProductBillingID int IDENTITY(1,1) PRIMARY KEY,
8> ProductBillingXML XML NOT NULL)
9> GO
1>
2> INSERT dbo.ProductBilling(ProductBillingXML)
3> VALUES ('<ProductBilling Billingnumber="1" customerid="22" orderdate="7/1/2005">
4~ <OrderItems>
5~ <Item id="22" qty="1" name="SQL"/>
6~ <Item id="24" qty="1" name="T-SQL"/>
7~ </OrderItems>
8~ </ProductBilling>')
9> GO

(1 rows affected)
1>
2> INSERT dbo.ProductBilling
3> (ProductBillingXML)
4> VALUES ('<ProductBilling Billingnumber="1" customerid="40" orderdate="7/11/2005">
5~ <OrderItems>
6~ <Item id="11" qty="1" name="Notes"/>
7~ </OrderItems>
8~ </ProductBilling>')
9> GO

(1 rows affected)
1>
2> INSERT dbo.ProductBilling
3> (ProductBillingXML)
4> VALUES ('<ProductBilling Billingnumber="1" customerid="9" orderdate="7/22/2005">
5~ <OrderItems>
6~ <Item id="11" qty="1" name="SQL Notes"/>
7~ <Item id="24" qty="1" name="T-SQL Notes"/>
8~ </OrderItems>
9~ </ProductBilling>')
10>
11> GO

(1 rows affected)
1> set quoted_identifier on
2> SELECT ProductBillingXML.query('/ProductBilling/OrderItems/Item') from dbo.ProductBilling
3> GO



------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
----------------
<Item id="22" qty="1" name="SQL"/><Item id="24" qty="1" name="T-SQL"/>


<Item id="11" qty="1" name="Notes"/>


<Item id="11" qty="1" name="SQL Notes"/><Item id="24" qty="1" name="T-SQL Notes"/>



(3 rows affected)
1>
2> set quoted_identifier off
3>
4> drop table dbo.ProductBilling
5> GO