DECLARE @ItemMessageXMLDECLARE @ItemTable TABLE(ItemNumberINT PRIMARY KEY, ItemDescriptionNVARCHAR( -)) SET @ItemMessage=N <ReceivablesInfos> <ReceivablesList> <LIFNR>0000xxxxxx</LIFNR> <NAME1> Shanghai Co., Ltd. </ Name1> <BUKRS>lllC</BUKRS> <BUTXT> Hunan Products Co., Ltd. </BUTXT> <CPUDT>2015-08-30</CPUDT&G T <DMBTR>-4850.68</DMBTR> <PSWSL>CNY</PSWSL> <datum>2016-07-14 16:37:59</datum> </ReceivablesList> <ReceivablesList> <LIFNR>0000100df0asd</LIFNR> <NAME1> Changsha ASDF Co., Ltd. </NAME1> <BUKRS>asdf</BUKRS> <BUTXT> Hunan Co., Ltd. </BUTXT> <cpudt>2015-08-30</cpud t> <DMBTR>-833.00</DMBTR> <PSWSL>CNY</PSWSL> <datum>2016-07-14 16:37:59</datu m> </ReceivablesList> <ReceivablesList> <LIFNR>0000asdfas</LIFNR> <NAME1> Guangzhou Limited public &L T;/name1> <BUKRS>asdf</BUKRS> <BUTXT> Hunan Products Co., Ltd. </BUTXT> <cpudt>2015-08-30</cpud T> <DMBTR>-DD.17</dmbtr> <PSWSL>CNY</PSWSL> <datum>2016-07-14 16:37:59</datum> </receivablesli st> <ReceivablesList> <LIFNR>00022252</LIFNR> <NAME1> Hunan D Co., Ltd. </NAME1> <bukrs& Gt;wer</bukrs> <BUTXT> Hunan D Co., Ltd. </BUTXT> <CPUDT>2015-08-30</CPUDT> <dmbtr>-232.5 1</dmbtr> <PSWSL>CNY</PSWSL> <datum>2016-07-14 16:37:59</datum> </receivableslist ></ReceivablesInfos>'SELECTT.c.value ('(Lifnr/text ()) [1]','VARCHAR (+)'), T.c.value ('(Name1/text ()) [1]','VARCHAR (+)' ) from @ItemMessage. Nodes ('/receivablesinfos/receivableslist') asT (c)
SQL reads XML, parses XML, SQL transforms XML into DataTable, SQL transforms XML into tables