xml to sql tables

Question

Need to convert xml into sql table.

Sample XML

<?xml version="1.0"?> <ConsumerTransactionList>   <ConsumerTransaction>
    <SourceSystem>
      <SourceTimestamp>2016-03-02T16:10:33+08:00</SourceTimestamp>
      <MarketCode>CHN</MarketCode>
    </SourceSystem>
    <TransactionHeader>
      <PersonnelCode>4215</PersonnelCode>
      <TransactionConsumer>
        <ConsumerId>15859625</ConsumerId>
      </TransactionConsumer>
    </TransactionHeader>
    <TransactionDetailList>
      <TransactionDetail>
        <LineNum>1</LineNum>
        <TransactionTypeCode>sle</TransactionTypeCode>
      </TransactionDetail>
      <TransactionDetail>
        <LineNum>2</LineNum>
        <TransactionTypeCode>rdm</TransactionTypeCode>
      </TransactionDetail>
    </TransactionDetailList>   </ConsumerTransaction>   <ConsumerTransaction>
    <SourceSystem>
      <SourceTimestamp>2016-03-02T16:12:27+08:00</SourceTimestamp>
      <MarketCode>KMU</MarketCode>
    </SourceSystem>
    <TransactionHeader>
      <PersonnelCode>4152</PersonnelCode>
      <TransactionConsumer>
        <ConsumerId>48578589</ConsumerId>
      </TransactionConsumer>
    </TransactionHeader>
    <TransactionDetailList>
      <TransactionDetail>
        <LineNum>1</LineNum>
        <TransactionTypeCode>sle</TransactionTypeCode>
      </TransactionDetail>
      <TransactionDetail>
        <LineNum>10</LineNum>
        <TransactionTypeCode>rdm</TransactionTypeCode>
      </TransactionDetail>
      <TransactionDetail>
        <LineNum>11</LineNum>
        <TransactionTypeCode>rdm</TransactionTypeCode>
      </TransactionDetail>
    </TransactionDetailList>   </ConsumerTransaction> </ConsumerTransactionList>

Expected output:

This is what I've tried so far:

This query generates permutation combination for the xml. Query need to populate a result set for each tag. But my query mingles all tags.Here only Im struggling.

SELECT  
x.item.value('SourceTimestamp[1]','varchar(100)'),  x.item.value('MarketCode[1]','varchar(100)'),
    y.item.value('PersonnelCode[1]','varchar(100)')
    ,z.item.value('ConsumerId[1]','varchar(100)'),  
    x1.item.value('LineNum[1]','varchar(100)'), x1.item.value('TransactionTypeCode[1]','varchar(100)') 

FROM 
 @x.nodes('//ConsumerTransaction/SourceSystem') AS x(item)
 cross join @x.nodes('//ConsumerTransaction/TransactionHeader') AS y(item)
 cross join @x.nodes('//ConsumerTransaction/TransactionHeader/TransactionConsumer') AS z(item)
 cross apply @x.nodes('//ConsumerTransaction/TransactionDetailList/TransactionDetail') as x1(item)

Possible duplicate of http://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server — Gnqz, May 10 '16 at 06:43
OK. We know what you want to achieve, but we don't know what have you tried. — Maciej Los, May 10 '16 at 06:43
@Gnqz : the link speaks about result in php. but i require in sql server — vignesh, May 10 '16 at 07:02
SQL server can also directly import XML documents https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/ or http://stackoverflow.com/questions/16838627/import-xml-into-sql-server — André Schild, May 10 '16 at 07:09
@AndréSchild The import of the XML is not in question... Your first link describes `FOR OPENXML`, which is outdated and the second does not deal with deeper nest levels... — Shnugo, May 10 '16 at 07:12
(Taken from an answer which will be deleted) To your code; 1) Be as specific as possible (avoid "//"). 2) You use `CROSS JOIN` while you should use `CROSS APPLY`. That's a big difference! 3) Nested levels need the .nodes() **for the nested nodes only** 4) You should name your out-columns. — Shnugo, May 10 '16 at 07:18

score 3 · Accepted Answer · answered May 10 '16 at 07:01

Assuming your XML is in a variable named @test you might try this:

SELECT CT.value('(SourceSystem/SourceTimestamp)[1]','datetime') AS SourceTimeStamp 
      ,CT.value('(SourceSystem/MarketCode)[1]','varchar(max)') AS MarketCode
      ,CT.value('(TransactionHeader/PersonnelCode)[1]','int') AS PersonnelCode
      ,CT.value('(TransactionHeader/TransactionConsumer/ConsumerId)[1]','int') AS ConsumerId
      ,TD.value('LineNum[1]','int') AS LineNum
      ,TD.value('TransactionTypeCode[1]','varchar(max)') AS TransactionTypeCode
FROM @test.nodes('/ConsumerTransactionList/ConsumerTransaction') AS A(CT)
CROSS APPLY CT.nodes('TransactionDetailList/TransactionDetail') AS B(TD)

One very good place to learn: http://www.stackoverflow.com :-) Try some googling with `(tsql OR "SQL Server") xml tutorial` The rest is trial and error... — Shnugo, May 11 '16 at 06:37

score 0 · Answer 2 · edited Mar 15 '19 at 20:49

Some idea for this example:

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://www.w3.org/2003/05/soap-envelope"
    xmlns:e="http://www.technisys.net/cmm/services/errors/v1.0" xmlns:md="http://www.technisys.net/cmm/services/metadata/v2.0"
    xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
    xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <SOAP-ENV:Header>
     <md:metadata><traceNumber>191750</traceNumber>
         <serviceId>processSystemUserLoginByCustomerMigration</serviceId>
         <serviceVersion>0.0</serviceVersion>
         <sessionId>OAUTH2_58f93b26-f876-42ab-aa41-2c9d482a199f_rriosc@bancofalabella.com.pe</sessionId>
         <targetChannel>
             <mnemonic>PF</mnemonic>
         </targetChannel>
         <executingChannel>
             <mnemonic>PF</mnemonic>
         </executingChannel>
         <address>ip=10.255.0.2;</address>
         <sourceDate>20190201101319013</sourceDate>
         <channelDispatchDate>xxxxxxxxxxxx</channelDispatchDate>
         <locale>es_AR</locale>
         <featureId>ROL@4702</featureId>
         <institutionType>UNDEFINED</institutionType>
         <msgTypeId>200</msgTypeId>
         <terminalId>browser=Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36;platform=;subChannel=;terminalCode=;</terminalId>
         <organizationId></organizationId>
         <organizationOperatorId>uriTech=OPERADOR_BANCO@9884015;uriSFB=150059641;branchCode=209;execUser=rriosc@bancofalabella.com.pe;execCodePosition=4;execNamePosition=EECC;execCodeRole=3;execNameRole=Ejecutivos Comerciales;</organizationOperatorId>
         <executingOperatorId>uriTech=OPERADOR_BANCO@9884015;uriSFB=150059641;branchCode=209;execUser=rriosc@bancofalabella.com.pe;execCodePosition=4;execNamePosition=EECC;execCodeRole=3;execNameRole=Ejecutivos Comerciales;</executingOperatorId>
         <paginationInfo></paginationInfo>
         <branchId>1</branchId>
         <userId></userId>
         <parityCurrencyId />
         <localCurrencyId />
         <localCountryId />
         <bankId />
         <organizationType/>
     </md:metadata>

    </SOAP-ENV:Header>
    <SOAP-ENV:Body>
     <srv:processSystemUserLoginByCustomerMigrationRequest xmlns:srv="http://www.technisys.net/cmm/services/processSystemUserLoginByCustomerMigration/rq/v0.0">
     <generic><sessionId type="java.lang.String">OAUTH2_58f93b26-f876-42ab-aa41-2c9d482a199f_rriosc@bancofalabella.com.pe</sessionId><forceToAdhere type="java.lang.Boolean">false</forceToAdhere></generic><customer name="customer"><identificationNumber>12345678</identificationNumber><identificationType><mnemonic>DNI</mnemonic></identificationType></customer>
     </srv:processSystemUserLoginByCustomerMigrationRequest>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

xml to sql tables

This is what I've tried so far:

2 Answers2