DECLARE @xml AS XML
set @xml= N'
<Invoices>
<Invoice Customer="Kim Abercrombie">
<Items>
<Item ProductID="2" Price="1.992" Quantity="1" />
<Item ProductID="3" Price="2.993" Quantity="2" />
<Item ProductID="5" Price="1.995" Quantity="1" />
</Items>
</Invoice>
<Invoice Customer="Margaret Smith">
<Items>
<Item ProductID="2" Price="1.992" Quantity="1"/>
</Items>
</Invoice>
</Invoices>
'
SELECT
[Book Name].value('../../@Customer','varchar(100)') AS Customer,
[Book Name].value('@ProductID','varchar(100)') AS ProductID,
[Book Name].value('@Price','float') AS Price,
[Book Name].value('@Quantity','varchar(100)') AS Quantity
FROM @xml.nodes('/Invoices/Invoice/Items/Item') Books([Book Name])
SQL SERVER XML
最新推荐文章于 2024-08-17 06:16:21 发布