0

I have a large XML file that I would like to get into SQL Server so I query it in a readable format for an end user. I have made a start on a query and i can get some data but unable to get the rest.

This is a sample set of data from my xml file

<file>
  <edxMsg>
    <edxMsgHdr>
      <trnNm>ICSHDR2</trnNm>
      <trnAct>N</trnAct>
      <trnVer>01</trnVer>
      <rcvAgncy>PLA</rcvAgncy>
      <rcvAgncyObjId>555012069A</rcvAgncyObjId>
      <sndAgncy>CLK</sndAgncy>
      <sndAgncyApplnId>ICS</sndAgncyApplnId>
      <sentDate>20180920</sentDate>
      <sentTime>013113615000</sentTime>
      <sendGMTOffsetMin>-600</sendGMTOffsetMin>
      <sndMchnId>N</sndMchnId>
      <trnRefId>37</trnRefId>
    </edxMsgHdr>
    <trnData>
      <batchInfo>
        <numRecords>1151</numRecords>
      </batchInfo>
    </trnData>
  </edxMsg>
  <edxMsg>
    <edxMsgHdr>
      <trnNm>ICS400CUR</trnNm>
      <trnAct>N</trnAct>
      <trnVer>01</trnVer>
      <rcvAgncy>PLA</rcvAgncy>
      <rcvAgncyObjId>2015</rcvAgncyObjId>
      <sndAgncy>CLK</sndAgncy>
      <sndAgncyObjId>204630959S</sndAgncyObjId>
      <sndAgncyApplnId>ICS</sndAgncyApplnId>
      <sndUserId>PLAN-AAD192</sndUserId>
      <sentDate>20180919</sentDate>
      <sentTime>131812085000</sentTime>
      <sendGMTOffsetMin>-600</sendGMTOffsetMin>
      <sndMchnId>N</sndMchnId>
      <trnRefId>37</trnRefId>
    </edxMsgHdr>
    <trnData>
      <respCurrent>
        <freeText>2015</freeText>
        <rqstDate>20180919</rqstDate>
        <rqstBtch>5575</rqstBtch>
        <dob>19690328</dob>
        <surname>Knocksville</surname>
        <firstname>Jonny</firstname>
        <procDts>20180919131812057</procDts>
        <partner>N</partner>
        <tpId>555012069A</tpId>
        <numChild>1</numChild>
        <shCareDtl>
          <chId>1</chId>
          <shPercent>100</shPercent>
        </shCareDtl>
        <maxRateDtl>
          <maxRateBen>DSP</maxRateBen>
          <maxRateInd>Y</maxRateInd>
        </maxRateDtl>
        <maxRateDtl>
          <maxRateBen>FTB</maxRateBen>
          <maxRateInd>Y</maxRateInd>
        </maxRateDtl>
        <payDtl>
          <paySts>PYD</paySts>
          <payType>REG</payType>
          <ben>DSP</ben>
          <grntDate>20100817</grntDate>
          <payDate>20180914</payDate>
          <payFreq>2WE</payFreq>
          <payActAmt>90760</payActAmt>
          <cmpDtl>
            <cmp>BASIC</cmp>
            <cmpPayAmt>82620</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>CES</cmp>
            <cmpPayAmt>1410</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>PNSUP</cmp>
            <cmpPayAmt>6730</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
        </payDtl>
        <payDtl>
          <paySts>PYD</paySts>
          <payType>REG</payType>
          <ben>FTB</ben>
          <grntDate>20000701</grntDate>
          <payDate>20180907</payDate>
          <payFreq>2WE</payFreq>
          <payLegAmt>51128</payLegAmt>
          <payActAmt>51128</payActAmt>
          <cmpDtl>
            <cmp>FTBA</cmp>
            <cmpPayAmt>23786</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>FTBA</cmp>
            <cmpPayAmt>23786</cmpPayAmt>
            <cmpPayCode>LEG</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>FTBB</cmp>
            <cmpPayAmt>10864</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>FTBB</cmp>
            <cmpPayAmt>10864</cmpPayAmt>
            <cmpPayCode>LEG</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>CESA</cmp>
            <cmpPayAmt>448</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>CESA</cmp>
            <cmpPayAmt>448</cmpPayAmt>
            <cmpPayCode>LEG</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>CESB</cmp>
            <cmpPayAmt>196</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>CESB</cmp>
            <cmpPayAmt>196</cmpPayAmt>
            <cmpPayCode>LEG</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>RA</cmp>
            <cmpPayAmt>15834</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>RA</cmp>
            <cmpPayAmt>15834</cmpPayAmt>
            <cmpPayCode>LEG</cmpPayCode>
          </cmpDtl>
        </payDtl>
        <payDtl>
          <paySts>NXT</paySts>
          <payType>REG</payType>
          <ben>DSP</ben>
          <grntDate>20100817</grntDate>
          <payDate>20180928</payDate>
          <payFreq>2WE</payFreq>
          <payActAmt>91195</payActAmt>
          <cmpDtl>
            <cmp>BASIC</cmp>
            <cmpPayAmt>83030</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>CES</cmp>
            <cmpPayAmt>1410</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>PNSUP</cmp>
            <cmpPayAmt>6755</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
        </payDtl>
        <payDtl>
          <paySts>NXT</paySts>
          <payType>REG</payType>
          <ben>FTB</ben>
          <grntDate>20000701</grntDate>
          <payDate>20180921</payDate>
          <payFreq>2WE</payFreq>
          <payLegAmt>51128</payLegAmt>
          <payActAmt>51128</payActAmt>
          <cmpDtl>
            <cmp>FTBA</cmp>
            <cmpPayAmt>23786</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>FTBA</cmp>
            <cmpPayAmt>23786</cmpPayAmt>
            <cmpPayCode>LEG</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>FTBB</cmp>
            <cmpPayAmt>10864</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>FTBB</cmp>
            <cmpPayAmt>10864</cmpPayAmt>
            <cmpPayCode>LEG</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>CESA</cmp>
            <cmpPayAmt>448</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>CESA</cmp>
            <cmpPayAmt>448</cmpPayAmt>
            <cmpPayCode>LEG</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>CESB</cmp>
            <cmpPayAmt>196</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>CESB</cmp>
            <cmpPayAmt>196</cmpPayAmt>
            <cmpPayCode>LEG</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>RA</cmp>
            <cmpPayAmt>15834</cmpPayAmt>
            <cmpPayCode>ACT</cmpPayCode>
          </cmpDtl>
          <cmpDtl>
            <cmp>RA</cmp>
            <cmpPayAmt>15834</cmpPayAmt>
            <cmpPayCode>LEG</cmpPayCode>
          </cmpDtl>
        </payDtl>
        <ddnDtl>
          <ddnBen>DSP</ddnBen>
          <ddnType>RCV</ddnType>
          <ddnAmt>9900</ddnAmt>
          <ddnDate>20180914</ddnDate>
        </ddnDtl>
        <ddnDtl>
          <ddnBen>DSP</ddnBen>
          <ddnType>DDF</ddnType>
          <ddnAmt>47319</ddnAmt>
          <ddnDate>20180914</ddnDate>
        </ddnDtl>
        <ddnDtl>
          <ddnBen>FTB</ddnBen>
          <ddnType>RCV</ddnType>
          <ddnAmt>6034</ddnAmt>
          <ddnDate>20180907</ddnDate>
        </ddnDtl>
        <ddnDtl>
          <ddnBen>FTB</ddnBen>
          <ddnType>DDF</ddnType>
          <ddnAmt>3000</ddnAmt>
          <ddnDate>20180907</ddnDate>
        </ddnDtl>
        <incDtl>
          <incType>FIN</incType>
          <incFreq>ANN</incFreq>
          <incAmt>525</incAmt>
          <incDate>20150320</incDate>
        </incDtl>
        <assDtl>
          <assType>CIS</assType>
          <assAmt>30000</assAmt>
          <assDate>20160920</assDate>
        </assDtl>
        <assDtl>
          <assType>HPE</assType>
          <assAmt>1000000</assAmt>
          <assDate>20100817</assDate>
        </assDtl>
      </respCurrent>
    </trnData>
  </edxMsg>
  </file>

This is my current code

DECLARE @x xml

SELECT @x = P
FROM OPENROWSET (BULK 'C:\Temp\XML\CCesResponse.xml', SINGLE_BLOB) AS Tenants (P)

SELECT @x

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

SELECT *
FROM OPENXML (@hdoc, 'file/edxMsg/trnData/respCurrent', 2)
WITH 
    (dob varchar(100),
     firstname varchar(100),
     surname varchar(100),
     payDtl varchar(500)
    )

I am trying to get a column for each of the following

file/edxMsg/trnData/respCurrent/payDtl/cmpDtl

I have tried what I think is everything but I must be missing something.

Any guidance would be great..

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ScottR
  • 31
  • 4
  • Please clarify "a readable format for an end user". Is [this wall](https://rextester.com/edit/FPK20709) has a readable format? – Alex Yu Feb 26 '19 at 00:43
  • Hi Alex, that helps but the reason that I am struggling is because I need the DOB Firstname Last name to be included for each line so I know who it belongs to.. thats the part I dont know how to do... – ScottR Feb 26 '19 at 02:14
  • I have similar issue reading xml from SQL. The best method is to use a programming language like c# to parse file and then write from c# to database. – jdweng Feb 26 '19 at 04:53

3 Answers3

1

The approach with FROM OPENXML (together with the two procedures to prepare and to remove a XML document) is outdated and should not be used anymore (rare exceptions exist).

Rather use the native XML methods provided by the XML data type.

Try this:

DECLARE  @xml XML=
N'place your XML here';

SELECT A.msg.value('(edxMsgHdr/trnNm)[1]','nvarchar(10)') AS trnNm
      ,A.msg.value('(edxMsgHdr/trnAct)[1]','nvarchar(10)') AS trnAct
      --Add all header values here
      ,A.msg.query('trnData') AS trnDataNode
FROM @xml.nodes('/file/edxMsg') A(msg);

The central idea is:

  • use .value() to retrieve a value from within the XML
  • use CROSS APPLY YourXml.nodes('Some XPath') to transform repeating elements into a derived set.
  • use .query() to retrieve a fragment of the XML

In this case I've used .nodes() to get two rows of <edxMsg>. All data within <edxMsgHdr> seems to be 1:1 related, so we can pick them easily using .value().

The difficult part will be the content of <trnData>.

The first message shows a simple <batchInfo> only, but the second message includes are very complex structure.

The problem is: This will need a lot of thinking as it seems to cover a complex 1:n related structure spread over several tables. Any repeating element (such as <maxRateDtl>, or <payDtl> must be retrieved in derived lists. The later has nested repeating elements itself (<cmpDtl>) and there are even more like <ddnDtl> and <assDtl>.

A query like the following would help you to get some data from within your <payDtl>:

SELECT A.msg.value('(edxMsgHdr/trnNm)[1]','nvarchar(10)') AS trnNm
      ,A.msg.value('(edxMsgHdr/trnAct)[1]','nvarchar(10)') AS trnAct
      ,A.msg.query('trnData') AS trnDataNode

      ,B.payDtl.value('(paySts)[1]','nvarchar(100)') AS paySts
      ,B.payDtl.value('(payType)[1]','nvarchar(100)') AS payType
      ,C.cmpDtl.value('(cmp)[1]','nvarchar(max)') AS cmp
      ,C.cmpDtl.value('(cmpPayAmt)[1]','decimal(10,4)') AS cmpPayAmt
FROM @xml.nodes('/file/edxMsg') A(msg)
CROSS APPLY A.msg.nodes('trnData/respCurrent/payDtl') B(payDtl)
CROSS APPLY B.payDtl.nodes('cmpDtl') C(cmpDtl)

Furthermore, you have to know the edxMsg-format really well to know, if there might be elements in your message not covered by this sample.

So: We can offer and explain the tools you need to extract the data, but you must know yourself, what a readable format is...

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • This is great, I was clearly way off the mark, thanks for your guidance and as you say now I have to find out how to put this in a readable format.. Appreciate this!! – ScottR Feb 27 '19 at 21:53
0

SQL server provides XML data type to store and search on XML data. If you want to search on XML in the SQL server, you can follow below steps -

  1. Create a table with XML datatype column
  2. Insert XML in this table
  3. Search on XML nodes as need

Note: If you have large data then you should also create XML indexes on XML column to improve the search performance.

You can refer below link for sample code

How can I query a value in SQL Server XML column

Mukesh Arora
  • 1,763
  • 2
  • 8
  • 19
0

You should really check out the built-in XQuery support - and toss the old, legacy OPENXML approach.

Try this to get all the <cmpDtl> nodes and extract the detail info from them:

SELECT
    Cmp = xc.value('(cmp)[1]', 'varchar(20)'),
    CmpPayAmount = xc.value('(cmpPayAmt)[1]', 'decimal(20,4)'),
    CmpPayCode = xc.value('(cmpPayCode)[1]', 'varchar(20)')
FROM
    @x.nodes('/file/edxMsg/trnData/respCurrent/payDtl/cmpDtl') AS XT(XC)

This returns a list of all values (three columns Cmp, CmpPayAmount, and CmpPayCode) from your XML, as proper relational data, which can then be e.g. inserted into a table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459