sql 解析xml

/

--属性读取表
DECLARE @x XML
SELECT @x =
'<Peoples>
  <People Name="tudou" Sex="女" />
  <People Name="choushuigou" Sex="女"/>
  <People Name="dongsheng" Sex="男" />
</Peoples>'
SELECT 
    x.v.value('@Name[1]','VARCHAR(20)') AS Name, 
    x.v.value('@Sex[1]','VARCHAR(20)') AS Sex
FROM @x.nodes('/Peoples/People') x(v)

SELECT v.value('@Name[1]','varchar(30)') FROM @x.nodes('/Peoples/People') t(v);
GO



/*
    节点读取表，读取数据时字段区分大小写，
    如带命名空间需要多加一行代码：WITH XMLNAMESPACES(DEFAULT 'http://cd.love.com/SOA') --带命名空间
*/
DECLARE @ItemMessage XML 
DECLARE @ItemTable TABLE(ItemNumber INT PRIMARY KEY,ItemDescription NVARCHAR(300))
SET @ItemMessage=N'
<ItemList xmlns="http://cd.love.com/SOA">     
<Item>
        <ItemNumber>1</ItemNumber>
        <ItemDescription>XBox 360,超值</ItemDescription>
    </Item>
    <Item>
        <ItemNumber>1</ItemNumber>
        <ItemDescription>Windows Phone7,快来尝鲜吧</ItemDescription> 
    </Item> 
</ItemList>';
WITH XMLNAMESPACES(DEFAULT 'http://cd.love.com/SOA') --带命名空间
SELECT 
T.c.value('(ItemNumber/text())[1]','int') AS ItemNumber, 
T.c.value('(ItemDescription/text())[1]','NVARCHAR(300)')  AS ItemDescription
FROM @ItemMessage.nodes('/ItemList/Item') AS T(c);

WITH XMLNAMESPACES(DEFAULT 'http://cd.love.com/SOA') --带命名空间
SELECT 
v.value('(ItemNumber/text())[1]','int') AS ItemNumber   FROM @ItemMessage.nodes('/ItemList/Item') t(v)
GO



DECLARE @ItemMessage XML ;
SET @ItemMessage =
'<SAPLOG>
    <rows><ISID>100</ISID><UPDDATE>2020-09-01 00:00:00</UPDDATE><SHOPNAME /><BSTKD>E20191119093</BSTKD><VBELN>0000100786</VBELN><VBELN_J>0080108510</VBELN_J><VBELN_M>0090095351</VBELN_M><KUNNR>0000100755</KUNNR><MATNR>000000000050001357</MATNR><KWMENG>0.500</KWMENG><DJ>145.00</DJ><WC>0.00</WC><ZK>-15.29</ZK><LGORT>C008</LGORT><STEP>C</STEP><MSGTYP>S</MSGTYP><MESSAGE>过账成功</MESSAGE></rows>
    <rows><ISID /><UPDDATE/><SHOPNAME /><BSTKD>E20191119093</BSTKD><VBELN>0000100786</VBELN><VBELN_J>0080108510</VBELN_J><VBELN_M>0090095351</VBELN_M><KUNNR>0000100755</KUNNR><MATNR>000000000050001357</MATNR><KWMENG>0.500</KWMENG><DJ>145.00</DJ><WC>0.00</WC><ZK>-15.29</ZK><LGORT>C008</LGORT><STEP>D</STEP><MSGTYP>S</MSGTYP><MESSAGE>开票成功</MESSAGE></rows>
    <rows><ISID /><UPDDATE/><SHOPNAME /><BSTKD>E20191119093</BSTKD><VBELN>0000100786</VBELN><VBELN_J>0080108510</VBELN_J><VBELN_M>0090095351</VBELN_M><KUNNR>0000100755</KUNNR><MATNR>000000000050001357</MATNR><KWMENG>0.500</KWMENG><DJ>145.00</DJ><WC>0.00</WC><ZK>-15.29</ZK><LGORT>C008</LGORT><STEP>B</STEP><MSGTYP>S</MSGTYP><MESSAGE>交货创建成功</MESSAGE></rows>
    <rows><ISID /><UPDDATE /><SHOPNAME /><BSTKD>E20191119093</BSTKD><VBELN>0000100786</VBELN><VBELN_J>0080108510</VBELN_J><VBELN_M>0090095351</VBELN_M><KUNNR>0000100755</KUNNR><MATNR>000000000050001554</MATNR><KWMENG>4.000</KWMENG><DJ>6.50</DJ><WC>0.00</WC><ZK>-5.48</ZK><LGORT>C008</LGORT><STEP>C</STEP><MSGTYP>S</MSGTYP><MESSAGE>过账成功</MESSAGE></rows>
</SAPLOG>' 
SELECT  
    t.c.value('(ISID/text())[1]','varchar(200)') AS ISID,
    t.c.value('(UPDDATE/text())[1]','varchar(200)') AS UPDDATE,
    t.c.value('(VBELN/text())[1]','varchar(200)') AS VBELN,
    t.c.value('(VBELN_J/text())[1]','varchar(200)') AS VBELN_J,
    t.c.value('(VBELN_M/text())[1]','varchar(200)') AS VBELN_M,
    t.c.value('(KUNNR/text())[1]','varchar(200)') AS KUNNR,
    t.c.value('(MATNR/text())[1]','varchar(200)') AS MATNR,
    t.c.value('(KWMENG/text())[1]','varchar(200)') AS KWMENG,
    t.c.value('(DJ/text())[1]','varchar(200)') AS DJ,
    t.c.value('(WC/text())[1]','varchar(200)') AS WC,
    t.c.value('(ZK/text())[1]','varchar(200)') AS ZK,
    t.c.value('(LGORT/text())[1]','varchar(200)') AS LGORT,
    t.c.value('(STEP/text())[1]','varchar(200)') AS STEP,
    t.c.value('(MSGTYP/text())[1]','varchar(200)') AS MSGTYP ,
    t.c.value('(MESSAGE/text())[1]','varchar(200)') AS [MESSAGE] 
FROM @ItemMessage.nodes('/SAPLOG/rows') AS t(c);

.