CODEWITHSUNDEEP
sql-serverxmlxquery
Teknas
Teknas

Reputation: 559

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

Upvotes: 2

Views: 136

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22311

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);

Upvotes: 3

persian-theme
persian-theme

Reputation: 6638

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

Upvotes: 2

Related Questions