none
Enter XML Data into SQL Server RRS feed

  • Question

  • here is the data:

    <?xml version="1.0"?>

    <BOMMAT04>


    <IDOC BEGIN="1">


    <EDI_DC40 SEGMENT="1">

    <TABNAM>EDI_DC40</TABNAM>

    <MANDT>100</MANDT>

    <DOCNUM>0000000000233154</DOCNUM>

    <DOCREL>701</DOCREL>

    <STATUS>30</STATUS>

    <DIRECT>1</DIRECT>

    <OUTMOD>2</OUTMOD>

    <IDOCTYP>BOMMAT04</IDOCTYP>

    <MESTYP>BOMMAT</MESTYP>

    <STDMES>BOMMAT</STDMES>

    <SNDPOR>SAPDEV</SNDPOR>

    <SNDPRT>LS</SNDPRT>

    <SNDPRN>DEVCLNT100</SNDPRN>

    <RCVPOR>ATHXIDEV</RCVPOR>

    <RCVPRT>LS</RCVPRT>

    <RCVPRN>RT3CLNT600</RCVPRN>

    <CREDAT>20191105</CREDAT>

    <CRETIM>113020</CRETIM>

    <SERIAL>201910031548490001</SERIAL>

    </EDI_DC40>


    <E1STZUM SEGMENT="1">

    <MSGFN>004</MSGFN>

    <STLTY>M</STLTY>

    <STLNR>00000128</STLNR>

    <STLAN>1</STLAN>

    <EXSTL> 1.000</EXSTL>

    <STLDT>20191003</STLDT>

    <STLTM>154808</STLTM>


    <E1MASTM SEGMENT="1">

    <MSGFN>004</MSGFN>

    <MATNR>INVPRODPARENT-001</MATNR>

    <WERKS>1111</WERKS>

    <STLAN>1</STLAN>

    <STLNR>00000128</STLNR>

    <STLAL>01</STLAL>

    <LOSVN>0.000</LOSVN>

    <LOSBS>0.000</LOSBS>

    </E1MASTM>


    <E1STKOM SEGMENT="1">

    <MSGFN>004</MSGFN>

    <STLAL>01</STLAL>

    <DATUV>20191003</DATUV>

    <BMEIN>NOS</BMEIN>

    <BMENG>000000000000000</BMENG>

    <STLST>01</STLST>

    <BMENG_C> 1.000</BMENG_C>

    </E1STKOM>


    <E1STPOM SEGMENT="1">

    <MSGFN>004</MSGFN>

    <STLKN>00000001</STLKN>

    <DATUV>20191003</DATUV>

    <IDNRK>INVPRODCHILD-001</IDNRK>

    <POSTP>L</POSTP>

    <POSNR>0010</POSNR>

    <MEINS>NOS</MEINS>

    <MENGE>000000000000000</MENGE>

    <AUSCH>0.00</AUSCH>

    <AVOAU>0.00</AVOAU>

    <SANFE>X</SANFE>

    <SANKA>X</SANKA>

    <NLFZT>0</NLFZT>

    <EWAHR>0</EWAHR>

    <LIFZT>0</LIFZT>

    <PREIS>0.00</PREIS>

    <PEINH>0</PEINH>

    <ROANZ>0.000</ROANZ>

    <ROMS1>0.000</ROMS1>

    <ROMS2>0.000</ROMS2>

    <ROMS3>0.000</ROMS3>

    <ROMEN>1.000</ROMEN>

    <OBJTY>1</OBJTY>

    <WEBAZ>0</WEBAZ>

    <CSSTR>0.00</CSSTR>

    <KNOBJ>000000000000000000</KNOBJ>

    <ALPRF>00</ALPRF>

    <CLSZU>00000000</CLSZU>

    <NLFZV>0</NLFZV>

    <MENGE_C> 1.000</MENGE_C>

    <ROMEN_C> 0.000</ROMEN_C>

    </E1STPOM>


    <E1STPOM SEGMENT="1">

    <MSGFN>004</MSGFN>

    <STLKN>00000002</STLKN>

    <DATUV>20191003</DATUV>

    <IDNRK>INVPRODCHILD-002</IDNRK>

    <POSTP>L</POSTP>

    <POSNR>0020</POSNR>

    <MEINS>NOS</MEINS>

    <MENGE>000000000000000</MENGE>

    <AUSCH>0.00</AUSCH>

    <AVOAU>0.00</AVOAU>

    <SANFE>X</SANFE>

    <SANKA>X</SANKA>

    <NLFZT>0</NLFZT>

    <EWAHR>0</EWAHR>

    <LIFZT>0</LIFZT>

    <PREIS>0.00</PREIS>

    <PEINH>0</PEINH>

    <ROANZ>0.000</ROANZ>

    <ROMS1>0.000</ROMS1>

    <ROMS2>0.000</ROMS2>

    <ROMS3>0.000</ROMS3>

    <ROMEN>1.000</ROMEN>

    <OBJTY>1</OBJTY>

    <WEBAZ>0</WEBAZ>

    <CSSTR>0.00</CSSTR>

    <KNOBJ>000000000000000000</KNOBJ>

    <ALPRF>00</ALPRF>

    <CLSZU>00000000</CLSZU>

    <NLFZV>0</NLFZV>

    <MENGE_C> 1.000</MENGE_C>

    <ROMEN_C> 0.000</ROMEN_C>

    </E1STPOM>

    </E1STZUM>

    </IDOC>

    </BOMMAT04>

    Wednesday, November 6, 2019 10:10 AM

Answers

All replies

  • here is the data:

    And where/what is the question / issue?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, November 6, 2019 10:57 AM
  • we have to load in the sql  table
    Wednesday, November 6, 2019 1:03 PM
  • You need to tell what are the results you expect in the tables and what are table structures.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, November 6, 2019 2:42 PM
  • Hi Pallavi Acharaya,

    Please clarify what exactly you are looking for. There are two ways to load XML into a database:

    1. Shredding XML into individual pieces and loading into a regular relational table(s).
      SSIS has XML Source Adapter to do that.
    2. Loading XML file in its entirety into a single row/column.
      SSIS has Execute SQL Task to do that.

    Wednesday, November 6, 2019 3:12 PM
  • Hi Pallavi Acharaya,

     

    Can you describe the final result you want? You can use openrowset to import xml file to sql table. For more details,please refer to https://www.mssqltips.com/sqlservertip/5707/simple-way-to-import-xml-data-into-sql-server-with-tsql/

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 7, 2019 6:56 AM