22,210
社区成员
发帖
与我相关
我的任务
分享
declare @x xml
set @x='
<SalesOrder CustomerID="18759" DueDate="2006-01-01T00:00:00" AccountNumber="10-4030-018759" ContactID="4189" BillToAddressID="14024" ShipToAddressID="14024" ShipMethodID="1" SubTotal="174.20" TaxAmt="10">
<Item>
<ProductID>714</ProductID>
<OrderQty>5</OrderQty>
<UnitPrice>28.84</UnitPrice>
</Item>
<Item>
<ProductID>715</ProductID>
<OrderQty>1</OrderQty>
<UnitPrice>30</UnitPrice>
</Item>
</SalesOrder>
';
select
t.c.value('./@CustomerID','int') CustomerID,
t.c.value('./@DueDate','datetime') DueDate,
t.c.value('./@AccountNumber','nvarchar(15)') AccountNumber,
t.c.value('./@ContactID','int') ContactID,
t.c.value('./@BillToAddressID','int') BillToAddressID,
t.c.value('./@ShipToAddressID','int') ShipToAddressID,
t.c.value('./@ShipMethodID','int') ShipMethodID,
t.c.value('./@SubTotal','money') SubTotal,
t.c.value('./@TaxAmt','money') TaxAmt
from @x.nodes('/SalesOrder') t(c);
select SalesOrderID [@SalesOrderID],OrderDate [@OrderDate],AccountNumber [@AccountNumber],
(select ProductID [@ProductID], OrderQty [@OrderQty] from SalesOrderDetail
where s.SalesOrderID=SalesOrderID for xml path('Item'),type)
from SalesOrderHeader s for xml path('SalesOrder'),type;
select soh.SalesOrderID, soh.OrderDate, soh.AccountNumber, sod.ProductID, sod.OrderQty
from SalesOrderHeader soh inner join SalesOrderDetail sod on soh.SalesOrderID=sod.SalesOrderID
for xml path('Order'),root('OrdersList');