Search code examples
sql-serverxmlxquery

XML to SQL Table Query

This is my XML stored in a row. How do I convert it to insert into a table using a T-SQL query in the following table format?

<ENVELOPE>

    <DSPVCHDATE>16-4-2021</DSPVCHDATE>
    <DSPVCHITEMACCOUNT>PRASHANT MEHTA 359244</DSPVCHITEMACCOUNT>
    <DSPVCHTYPE>Sale</DSPVCHTYPE>
    <DSPINBLOCK>
        <DSPVCHINQTY></DSPVCHINQTY>
        <DSPVCHINAMT></DSPVCHINAMT>
    </DSPINBLOCK>
    <DSPOUTBLOCK>
        <DSPVCHOUTQTY>1 Pcs</DSPVCHOUTQTY>
        <DSPVCHNETTOUTAMT>23046.88</DSPVCHNETTOUTAMT>
    </DSPOUTBLOCK>
    <DSPCLBLOCK>
        <DSPVCHCLQTY></DSPVCHCLQTY>
        <DSPVCHCLAMT></DSPVCHCLAMT>
    </DSPCLBLOCK>
    <DSPEXPLVCHNUMBER>(No. :IV2612)</DSPEXPLVCHNUMBER>


    <DSPVCHDATE>19-4-2021</DSPVCHDATE>
    <DSPVCHITEMACCOUNT>XYZ Company</DSPVCHITEMACCOUNT>
    <DSPVCHTYPE>Purchase</DSPVCHTYPE>
    <DSPINBLOCK>
        <DSPVCHINQTY>1 Pcs</DSPVCHINQTY>
        <DSPVCHINAMT>23437.50</DSPVCHINAMT>
    </DSPINBLOCK>
    <DSPOUTBLOCK>
        <DSPVCHOUTQTY></DSPVCHOUTQTY>
        <DSPVCHNETTOUTAMT></DSPVCHNETTOUTAMT>
    </DSPOUTBLOCK>
    <DSPCLBLOCK>
        <DSPVCHCLQTY>0 Pcs</DSPVCHCLQTY>
        <DSPVCHCLAMT></DSPVCHCLAMT>
    </DSPCLBLOCK>
    <DSPEXPLVCHNUMBER>(No. :IV2613)</DSPEXPLVCHNUMBER>
</ENVELOPE>

This is the required output format.

Issue is I do not have a record separator in raw xml. Each new records starts with a <DSPVCHDATE>

enter image description here

Solution

  • You can use outer apply to navigate the nested elements of xml content. Given the inconvenient structure of this XML, it can be changed into something useable as follows, by adding a containing node called <ThisNode>.

    DECLARE @XML XML = '
<ENVELOPE>

    <DSPVCHDATE>16-4-2021</DSPVCHDATE>
    <DSPVCHITEMACCOUNT>PRASHANT MEHTA 359244</DSPVCHITEMACCOUNT>
    <DSPVCHTYPE>Sale</DSPVCHTYPE>
    <DSPINBLOCK>
      <DSPVCHINQTY></DSPVCHINQTY>
      <DSPVCHINAMT></DSPVCHINAMT>
    </DSPINBLOCK>
    <DSPOUTBLOCK>
      <DSPVCHOUTQTY>1 Pcs</DSPVCHOUTQTY>
      <DSPVCHNETTOUTAMT>23046.88</DSPVCHNETTOUTAMT>
    </DSPOUTBLOCK>
    <DSPCLBLOCK>
      <DSPVCHCLQTY></DSPVCHCLQTY>
      <DSPVCHCLAMT></DSPVCHCLAMT>
    </DSPCLBLOCK>
    <DSPEXPLVCHNUMBER>(No. :IV2612)</DSPEXPLVCHNUMBER>


    <DSPVCHDATE>19-4-2021</DSPVCHDATE>
    <DSPVCHITEMACCOUNT>XYZ Company</DSPVCHITEMACCOUNT>
    <DSPVCHTYPE>Purchase</DSPVCHTYPE>
    <DSPINBLOCK>
      <DSPVCHINQTY>1 Pcs</DSPVCHINQTY>
      <DSPVCHINAMT>23437.50</DSPVCHINAMT>
    </DSPINBLOCK>
    <DSPOUTBLOCK>
      <DSPVCHOUTQTY></DSPVCHOUTQTY>
      <DSPVCHNETTOUTAMT></DSPVCHNETTOUTAMT>
    </DSPOUTBLOCK>
    <DSPCLBLOCK>
      <DSPVCHCLQTY>0 Pcs</DSPVCHCLQTY>
      <DSPVCHCLAMT></DSPVCHCLAMT>
    </DSPCLBLOCK>
    <DSPEXPLVCHNUMBER>(No. :IV2613)</DSPEXPLVCHNUMBER>

</ENVELOPE>'

    This can be converted to useable XML as follows:

    WITH 
cte AS (Select REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), @XML, 1), N'<DSPVCHDATE>', '
  </ThisNode>
  <ThisNode>
    <DSPVCHDATE>'), N'</ENVELOPE>', N'
  </ThisNode>
</ENVELOPE>')  AS str)
SELECT @XML = CAST(STUFF(str, CHARINDEX(N'</ThisNode>', str), LEN(N'</ThisNode>'), N'') AS XML)
FROM cte
;

    query

    SELECT
      A.evnt.value('(DSPVCHDATE/text())[1]','nvarchar(100)') as DSPVCHDATE
     ,A.evnt.value('(DSPVCHITEMACCOUNT/text())[1]','nvarchar(100)') as DSPVCHITEMACCOUNT
     ,A.evnt.value('(DSPVCHTYPE/text())[1]','nvarchar(100)') as DSPVCHTYPE
     ,A.evnt.value('(DSPVCHITEMACCOUNT/text())[1]','nvarchar(100)') as DSPVCHITEMACCOUNT
     ,A.evnt.value('(DSPEXPLVCHNUMBER/text())[1]','nvarchar(100)') as DSPEXPLVCHNUMBER

     ,B.rec.value('(DSPVCHINQTY/text())[1]','nvarchar(100)') AS DSPVCHINQTY
     ,B.rec.value('(DSPVCHINAMT/text())[1]','nvarchar(100)') AS DSPVCHINAMT 

     ,C.rec.value('(DSPVCHOUTQTY/text())[1]','nvarchar(100)') AS DSPVCHOUTQTY 
     ,C.rec.value('(DSPVCHNETTOUTAMT/text())[1]','float') AS DSPVCHNETTOUTAMT 

     ,D.rec.value('(DSPVCHCLQTY/text())[1]','nvarchar(100)') AS DSPVCHCLQTY 
     ,D.rec.value('(DSPVCHCLAMT/text())[1]','int') AS DSPVCHCLAMT 

FROM @XML.nodes('/ENVELOPE/ThisNode') A(evnt)
OUTER APPLY A.evnt.nodes('DSPINBLOCK') B(rec)
OUTER APPLY A.evnt.nodes('DSPOUTBLOCK') C(rec)
OUTER APPLY A.evnt.nodes('DSPCLBLOCK') D(rec)

    demo in db<>fiddle