SqL读取XML、解析XML、SqL将XML转换DataTable、SqL将XML转换表

DECLARE @ItemMessage XML 
DECLARE @ItemTable TABLE(ItemNumber INT PRIMARY KEY,ItemDescription NVARCHAR(300))
  
SET @ItemMessage=N'
<ReceivablesInfos>
  <ReceivablesList>
    <LIFNR>0000xxxxxx</LIFNR>
    <NAME1>上海有限公司</NAME1>
    <BUKRS>lllC</BUKRS>
    <BUTXT>湖南用品有限公司</BUTXT>
    <CPUDT>2015-08-30</CPUDT>
    <DMBTR>-4850.68</DMBTR>
    <PSWSL>CNY</PSWSL>
    <DATUM>2016-07-14 16:37:59</DATUM>
  </ReceivablesList>
  <ReceivablesList>
    <LIFNR>0000100df0asd</LIFNR>
    <NAME1>长沙asdf有限公司</NAME1>
    <BUKRS>asdf</BUKRS>
    <BUTXT>湖南有限公司</BUTXT>
    <CPUDT>2015-08-30</CPUDT>
    <DMBTR>-833.00</DMBTR>
    <PSWSL>CNY</PSWSL>
    <DATUM>2016-07-14 16:37:59</DATUM>
  </ReceivablesList>
  <ReceivablesList>
    <LIFNR>0000asdfas</LIFNR>
    <NAME1>广州有限公 </NAME1>
    <BUKRS>asdf</BUKRS>
    <BUTXT>湖南用品有限公司</BUTXT>
    <CPUDT>2015-08-30</CPUDT>
    <DMBTR>-dd.17</DMBTR>
    <PSWSL>CNY</PSWSL>
    <DATUM>2016-07-14 16:37:59</DATUM>
  </ReceivablesList>
  <ReceivablesList>
    <LIFNR>00022252</LIFNR>
    <NAME1>湖南d有限公司</NAME1>
    <BUKRS>wer</BUKRS>
    <BUTXT>湖南d有限公司</BUTXT>
    <CPUDT>2015-08-30</CPUDT>
    <DMBTR>-232.51</DMBTR>
    <PSWSL>CNY</PSWSL>
    <DATUM>2016-07-14 16:37:59</DATUM>
  </ReceivablesList>
</ReceivablesInfos>
'

SELECT T.c.value('(LIFNR/text())[1]','VARCHAR(1000)' ), 
T.c.value('(NAME1/text())[1]','VARCHAR(1000)' ) 
FROM @ItemMessage.nodes('/ReceivablesInfos/ReceivablesList') AS T(c)

 

转载于:https://www.cnblogs.com/lanyubaicl/p/5671966.html