SQL 解析 XML

1.使用nodes和value解析XML

<!--ItemsXml column content something like this-->
<GoodsReceiveNoteItemView>
  <Location>KRCK</Location>
  <ProductName>Soon Hock, Live 800g - 1.5kg</ProductName>
  <UomCode>KG</UomCode>
  <Quantity>2.00</Quantity>
  <UnitCost>33.000000</UnitCost>
</GoodsReceiveNoteItemView>
<GoodsReceiveNoteItemView>
  <Location>KRCK</Location>
  <ProductName>Prawn -  Live</ProductName>
  <UomCode>KG</UomCode>
  <Quantity>2.00</Quantity>
  <UnitCost>20.000000</UnitCost>
</GoodsReceiveNoteItemView>
<GoodsReceiveNoteItemView>
  <Location>KRCK</Location>
  <ProductName>Sea Bass - Live</ProductName>
  <UomCode>KG</UomCode>
  <Quantity>3.80</Quantity>
  <UnitCost>11.000000</UnitCost>
</GoodsReceiveNoteItemView>
<GoodsReceiveNoteItemView>
  <Location>KRCK</Location>
  <ProductName>Soon Hock - Live (400 to 700gm)</ProductName>
  <UomCode>KG</UomCode>
  <Quantity>1.70</Quantity>
  <UnitCost>31.000000</UnitCost>
</GoodsReceiveNoteItemView>
<GoodsReceiveNoteItemView>
  <Location>KRCK</Location>
  <ProductName>Garoupa Tiger Live</ProductName>
  <UomCode>KG</UomCode>
  <Quantity>8.20</Quantity>
  <UnitCost>23.000000</UnitCost>
</GoodsReceiveNoteItemView>

 

;with t_table as
(
    select (CONVERT(xml,ItemsXml)) ix from wh.vw_ReportGoodsReceiveNote
)
select (select sum( t.value('Quantity[1]','decimal(18,2)')*t.value('UnitCost[1]','decimal(18,2)')) FROM ix.nodes('GoodsReceiveNoteItemView') x(t)) as TotalCost
from t_table 

2.单行转多行

;with t_table as
(
    select (CONVERT(xml,ItemsXml)) ix
    from wh.vw_ReportGoodsReceiveNote 
)
SELECT 
(select sum( t.value('Quantity[1]','decimal(18,2)')*t.value('UnitCost[1]','decimal(18,2)')) FROM t1.ix.nodes('GoodsReceiveNoteItemView') x(t)) as TotalCost
,t2.*
from t_table t1
OUTER APPLY(
    SELECT t.value('ProductName[1]','varchar(200)') as GRN_Product
    ,t.value('Quantity[1]','decimal(18,2)') as GRN_Qty
    ,t.value('UomCode[1]','varchar(200)') as GRN_Unit 
    ,(t.value('UnitCost[1]','decimal(18,2)')*t.value('Quantity[1]','decimal(18,2)')) as GRN_Amount 
    FROM t1.ix.nodes('GoodsReceiveNoteItemView') x(t)
) t2