Скачать или смотреть T-SQL: Extracting values from XML with multiple nodes

FULL TSQL SCRIPT IS HERE: www.dargelis.net/idea/sql-scripts/XML_select_2.sql

DECLARE @xml_str xml
SET @xml_str='
?shiporder?
?orderid?889923?/orderid?
?orderperson?John Smith?/orderperson?
?shipto?
?name?Alexey?/name?
?address?Champ elysee 1?/address?
?city?Paris?/city?
?country?France?/country?
?/shipto?
?lines?
?item?
?product?Lager format printer?/product?
?note?Special Edition?/note?
?quantity?2?/quantity?
?price?1090?/price?
?/item?
?item?
?product?Big boxes?/product?
?quantity?100?/quantity?
?price?9.90?/price?
?/item?
?item?
?product?iPhone?/product?
?note?model X?/note?
?quantity?2?/quantity?
?price?1234.56?/price?
?/item?
?/lines?
?/shiporder?'



DECLARE @PRODUCTID nvarchar(50)
DECLARE @QTY int
DECLARE @PRICE decimal(18,2)

DECLARE LineCursor CURSOR FOR
select
Lines.value('product[1]', 'nvarchar(50)') AS ProductID,
Lines.value('quantity[1]', 'int') AS Qty,
Lines.value('price[1]', 'decimal(18,2)') AS Price
from @xml_str.nodes('/shiporder/lines/item') as SalesOrder(Lines)

OPEN LineCursor
FETCH NEXT FROM LineCursor INTO @PRODUCTID,@QTY,@PRICE

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @PRODUCTID,@QTY,@PRICE

FETCH NEXT FROM LineCursor INTO @PRODUCTID,@QTY,@PRICE
END
CLOSE LineCursor
DEALLOCATE LineCursor