디비랑[dɪ'bɪraŋ]

/********************************************************************************************
-- Title : [2k5] XML에서 .Nodes()를 이용한 데이터 추출
-- Reference : bol
-- Key word : xquery xml nodes
********************************************************************************************/
DECLARE @xmlStr xml;

SET @xmlStr ='
<Root>
  <Order OrderID="1000" OrderDate="2005-06-04">
    <LineItem ProductID="1" Price="2.99" Quantity="3"/>
    <LineItem ProductID="2" Price="3.99" Quantity="1"/>
  </Order>
  <Order OrderID="1001" OrderDate="2005-06-05">
    <LineItem ProductID="3" Price="12.99" Quantity="13"/>
    <LineItem ProductID="4" Price="13.99" Quantity="11"/>
  </Order>
  <Order OrderID="1002" OrderDate="2005-06-06">
    <LineItem ProductID="5" Price="23.99" Quantity="21">
      <ItemName Name="자전거" color="빨강"/>
    </LineItem>
  </Order>
</Root>'

--Case 1. 
SELECT nCol.value('@ProductID',' integer') ProductID
     , nCol.value('@Quantity',' integer') Quantity
FROM @xmlStr.nodes('/Root/Order/LineItem') AS nTbl(nCol);
                 /* ='//Order/LineItem' */

--Case 2.
SELECT nTbl.nCol.query('.') AS result
FROM   @xmlStr.nodes('/Root/Order') nTbl(nCol);
                   /* ='//Order' */

--Case 3.
SELECT nTbl.nCol.value('@OrderID','int') as id,
       nTbl.nCol.query('LineItem') as NAME
FROM   @xmlStr.nodes('/Root/Order') nTbl(nCol);

--Case 4.
SELECT nTbl.nCol.value('@OrderID','int') as id      
FROM @xmlStr.nodes('/Root/Order') nTbl(nCol)      
WHERE nTbl.nCol.exist('LineItem') = 1;   -- LineItem이 있는거   

--Case 5.
SELECT nTbl.nCol.value('@OrderID','int') "OrderID"
     , nTbl2.nCol2.value('@ProductID','int') "ProductID"      
FROM @xmlStr.nodes('/Root/Order') nTbl(nCol) 
OUTER APPLY nTbl.nCol.nodes('./LineItem') as nTbl2(nCol2);    
  

--Case 6.
SELECT nTbl.nCol.value('@OrderID','int') "OrderID"
     , nTbl.nCol.value('(./LineItem/@Price)[1]','float') "Price"
     , nTbl2.nCol2.value('@ProductID','int') "ProductID"   
     , nTbl2.nCol2.value('(./ItemName/@Name)[1]','varchar(10)') "Name"  
FROM @xmlStr.nodes('/Root/Order') nTbl(nCol) 
OUTER APPLY nTbl.nCol.nodes('//LineItem') as nTbl2(nCol2)
Order by 1;