Insert XML data to database : XML « XML « SQL Server / T-SQL Tutorial

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

Retrieving XML Data
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>
10> INSERT dbo.ProductBilling
11> (ProductBillingXML)
12> VALUES ('<ProductBilling Billingnumber="1" customerid="40" orderdate="7/11/2005">
13~ <OrderItems>
14~ <Item id="11" qty="1" name="Notes"/>
15~ </OrderItems>
16~ </ProductBilling>')
17>
18> INSERT dbo.ProductBilling
19> (ProductBillingXML)
20> VALUES ('<ProductBilling Billingnumber="1" customerid="9" orderdate="7/22/2005">
21~ <OrderItems>
22~ <Item id="11" qty="1" name="SQL Notes"/>
23~ <Item id="24" qty="1" name="T-SQL Notes"/>
24~ </OrderItems>
25~ </ProductBilling>')
26>
27> select * from dbo.ProductBilling
28> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)
ProductBillingID ProductBillingXML


---------------- -------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
---------------------------------
               1 <ProductBilling Billingnumber="1" customerid="22" orderdate="7/1/2005"><OrderItems><Item id="22" qty="1
" name="SQL"/><Item id="24" qty="1" name="T-SQL"/></OrderItems></ProductBilling>

               2 <ProductBilling Billingnumber="1" customerid="40" orderdate="7/11/2005"><OrderItems><Item id="11" qty="
1" name="Notes"/></OrderItems></ProductBilling>

               3 <ProductBilling Billingnumber="1" customerid="9" orderdate="7/22/2005"><OrderItems><Item id="11" qty="1
" name="SQL Notes"/><Item id="24" qty="1" name="T-SQL Notes"/></OrderItems></ProductBilling>


(3 rows affected)
1>
2> drop table dbo.ProductBilling
3> GO