XML to SQL Table Query

Question

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>

This is a very annoying, and really badly formatted XML - it would be much easier if each row you want to extract where inside its own separate XML element, e.g. <DATAROW><DSPVCHDATE> ...... etc. With the current format, it's next to impossible to get a proper output - at least in T-SQL. Can you influence the shape of the incoming XML in any way? — marc_s
– marc_s, Commented Jul 17, 2021 at 8:21
Issue is I do not have control over XML output. 3rd party software sends this XML data which I do not have control. I agree with you. But I have to find some way. — Teknas
– Teknas, Commented Jul 17, 2021 at 8:29

Yitzhak Khabinsky · Accepted Answer · 2021-07-18 17:02:15Z

3

Here is another method by using pure XQuery. No need to do any string manipulation, CASTing, etc.

All elements inside the root element <ENVELOPE> constitute an Arithmetic Progression. Elements that grouped by their position: 1 - 7, 8 - 14, etc. should be placed inside the encompassing <row> element.

It creates the following XML on the fly:

<ENVELOPE>
  <row>
    <DSPVCHDATE>16-4-2021</DSPVCHDATE>
    ...
    <DSPEXPLVCHNUMBER>(No. :IV2612)</DSPEXPLVCHNUMBER>
  </row>
  <row>
    <DSPVCHDATE>19-4-2021</DSPVCHDATE>
    ...
    <DSPEXPLVCHNUMBER>(No. :IV2613)</DSPEXPLVCHNUMBER>
  </row>
</ENVELOPE>

SQL

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<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>');

SELECT ID --, x
    , c.value('(DSPVCHDATE/text())[1]','nvarchar(100)') as DSPVCHDATE
    ,c.value('(DSPVCHITEMACCOUNT/text())[1]','nvarchar(100)') as DSPVCHITEMACCOUNT
    ,c.value('(DSPVCHTYPE/text())[1]','nvarchar(100)') as DSPVCHTYPE
    ,c.value('(DSPINBLOCK/DSPVCHINQTY/text())[1]','nvarchar(100)') AS DSPVCHINQTY
    ,c.value('(DSPINBLOCK/DSPVCHINAMT/text())[1]','decimal(12,2)') AS DSPVCHINAMT 
    ,c.value('(DSPOUTBLOCK/DSPVCHOUTQTY/text())[1]','nvarchar(100)') AS DSPVCHOUTQTY 
    ,c.value('(DSPOUTBLOCK/DSPVCHNETTOUTAMT/text())[1]','decimal(12,2)') AS DSPVCHNETTOUTAMT
    ,c.value('(DSPEXPLVCHNUMBER/text())[1]','nvarchar(100)') as DSPEXPLVCHNUMBER
    --,c.value('(DSPCLBLOCK/DSPVCHCLQTY/text())[1]','nvarchar(100)') AS DSPVCHCLQTY 
    --,c.value('(DSPCLBLOCK/DSPVCHCLAMT/text())[1]','int') AS DSPVCHCLAMT 
FROM @tbl
CROSS APPLY (SELECT xmldata.query('<ENVELOPE>
    {
        for $x in /ENVELOPE/DSPVCHDATE
        let $pos := count(ENVELOPE/DSPVCHDATE[. << $x]) + 1
        let $start := 1 + 7 * ($pos -1)
        let $end := 7 * $pos
        return <row>{/ENVELOPE/*[position() ge $start and position() le $end]}</row>
    }
    </ENVELOPE>')) AS t1(x)
CROSS APPLY t1.x.nodes('/ENVELOPE/row') AS t2(c);

Output

+----+------------+-----------------------+------------+-------------+-------------+--------------+------------------+------------------+
| ID | DSPVCHDATE |   DSPVCHITEMACCOUNT   | DSPVCHTYPE | DSPVCHINQTY | DSPVCHINAMT | DSPVCHOUTQTY | DSPVCHNETTOUTAMT | DSPEXPLVCHNUMBER |
+----+------------+-----------------------+------------+-------------+-------------+--------------+------------------+------------------+
|  1 | 16-4-2021  | PRASHANT MEHTA 359244 | Sale       | NULL        | NULL        | 1 Pcs        | 23046.88         | (No. :IV2612)    |
|  1 | 19-4-2021  | XYZ Company           | Purchase   | 1 Pcs       | 23437.50    | NULL         | NULL             | (No. :IV2613)    |
+----+------------+-----------------------+------------+-------------+-------------+--------------+------------------+------------------+

SQL #2

Based on @Charlieface idea.

WITH rs AS
(
    SELECT ID, xmldata
        , c.value('for $i in . return count(../*[. << $i]) + 1', 'INT') AS pos
    FROM @tbl
        CROSS APPLY xmldata.nodes('/ENVELOPE/DSPVCHDATE') AS t(c)
) 
SELECT ID
    , c.value('(/ENVELOPE/*[sql:column("pos")]/text())[1]','nvarchar(100)') AS DSPVCHDATE
    , c.value('(/ENVELOPE/*[sql:column("pos") + 1]/text())[1]','nvarchar(100)') AS DSPVCHITEMACCOUNT
    , c.value('(/ENVELOPE/*[sql:column("pos") + 2]/text())[1]','nvarchar(100)') AS DSPVCHTYPE
    , c.value('(/ENVELOPE/*[sql:column("pos") + 3]/DSPVCHINQTY/text())[1]','nvarchar(100)') AS DSPVCHINQTY
    , c.value('(/ENVELOPE/*[sql:column("pos") + 3]/DSPVCHINAMT/text())[1]','decimal(12,2)') AS DSPVCHINAMT 
    , c.value('(/ENVELOPE/*[sql:column("pos") + 4]/DSPVCHOUTQTY/text())[1]','nvarchar(100)') AS DSPVCHOUTQTY 
    , c.value('(/ENVELOPE/*[sql:column("pos") + 4]/DSPVCHNETTOUTAMT/text())[1]','nvarchar(100)') AS DSPVCHNETTOUTAMT
    , c.value('(/ENVELOPE/*[sql:column("pos") + 6]/text())[1]','nvarchar(100)') AS DSPEXPLVCHNUMBER
FROM rs
    CROSS APPLY xmldata.nodes('/ENVELOPE') AS t(c);

edited Jul 18, 2021 at 17:02

answered Jul 18, 2021 at 3:42

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Charlieface Over a year ago

Can't you shorten this by just returning each full set of values in one shot from XQuery?

Yitzhak Khabinsky Over a year ago

Your point is not clear. Please elaborate

Charlieface Over a year ago

I feel like there must be a way of iterating the 7 rows with only one CROSS APPLY, by changing .query to .nodes

Yitzhak Khabinsky Over a year ago

@Charlieface, Indeed, there is a solution based on your idea. I updated the answer.

RBarryYoung Over a year ago

Very nice. I knew there was a way to do this with XQuery, but I don't know XQuery procs well enough to know what it was.

|

persian-theme · Accepted Answer · 2021-07-17 10:26:11Z

2

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

edited Jul 17, 2021 at 10:26

answered Jul 17, 2021 at 9:13

persian-theme

6,6483 gold badges9 silver badges17 bronze badges

7 Comments

persian-theme Over a year ago

Yes, the structure is quite unpleasant and only causes the element to repeat.

RBarryYoung Over a year ago

If you can come up with a way to convert the OP's XML to your XML in SQL Server automatically, then you could apply the rest of your answer. I think that I can show how to do that, if you want.

persian-theme Over a year ago

Yes, if possible, you can show me the automatic xml conversion

RBarryYoung Over a year ago

If it's already in SQL Server then this is the correct way.

RBarryYoung Over a year ago

@MeysamAsadi I have updated your answer with my suggestion.

|

Collectives™ on Stack Overflow

XML to SQL Table Query

2 Answers 2

8 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related