SQL Insert From XML Into Table

Question

I am using MS-SQL. I have xml which has structure survey, section, rows, cells. I want to import the values from nodes into three tables which are related dbo.sections, dbo.rows, dbo.cells. For this purpose I have to iterate over this xml. First insert the section and get the scode_indentity() and use it for the insert for child row as FK to inserted section. Then get cell inside the inserted row and to insert it with FK scope_indentity() (id from the inserted parent row node). What is the best practice in this situation to map nodes parent to child, insert and get scope_indentity() and pass it to child values for the insert statement ?

Tables DESCRIPTION:

dbo.sections

dbo.rows -> FK to sections

dbo.cells -> FK to rows

XML DESCRIPTION:

<survey S_VALUE1="45" S_VALUE2="1" S_VALUE3="1">
    <section SE_VALUE1="34" SE_VALUE2="1855436" SE_VALUE3="False">
        <row R_VALUE1="29" R_VALUE2="7444255">
            <cell C_VALUE1="43830582" C_VALUE2="28" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="29" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="30" C_VALUE3="1" />
        </row>
        <row R_VALUE1="30" R_VALUE2="7444255">
            <cell C_VALUE1="43830582" C_VALUE2="31" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="32" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="33" C_VALUE3="1" />
        </row>
        <row R_VALUE1="31" R_VALUE2="7444255">
            <cell C_VALUE1="43830582" C_VALUE2="34" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="35" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="36" C_VALUE3="1" />
        </row>
    </section>
    <section SE_VALUE1="35" SE_VALUE2="1855436" SE_VALUE3="False">
        <row R_VALUE1="32" R_VALUE2="7444255"/>
        <row R_VALUE1="33" R_VALUE2="7444255"/>
        <row R_VALUE1="34" R_VALUE2="7444255"/>
    </section>
</survey>

Possible duplicate of How to import XML file into MySQL database table using XML_LOAD(); function — user170442
– user170442, Commented Mar 15, 2016 at 8:39

Devart · Accepted Answer · 2016-03-15 10:04:30Z

3

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
    DROP TABLE #temp
GO

DECLARE @x XML = '
<survey S_VALUE1="45" S_VALUE2="1" S_VALUE3="1">
    <section SE_VALUE1="34" SE_VALUE2="1855436" SE_VALUE3="False">
        <row R_VALUE1="29" R_VALUE2="7444255">
            <cell C_VALUE1="43830582" C_VALUE2="28" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="29" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="30" C_VALUE3="1" />
        </row>
        <row R_VALUE1="30" R_VALUE2="7444255">
            <cell C_VALUE1="43830582" C_VALUE2="31" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="32" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="33" C_VALUE3="1" />
        </row>
        <row R_VALUE1="31" R_VALUE2="7444255">
            <cell C_VALUE1="43830582" C_VALUE2="34" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="35" C_VALUE3="1" />
            <cell C_VALUE1="43830582" C_VALUE2="36" C_VALUE3="1" />
        </row>
    </section>
    <section SE_VALUE1="35" SE_VALUE2="1855436" SE_VALUE3="False">
        <row R_VALUE1="32" R_VALUE2="7444255" />
        <row R_VALUE1="33" R_VALUE2="7444255" />
        <row R_VALUE1="34" R_VALUE2="7444255" />
    </section>
</survey>'

SELECT
      SE_VALUE1 = t.c.value('@SE_VALUE1', 'INT')
    , SE_VALUE2 = t.c.value('@SE_VALUE2', 'INT')
    , SE_VALUE3 = t.c.value('@SE_VALUE3', 'BIT')
    , R_VALUE1 = t2.c2.value('@R_VALUE1', 'INT')
    , R_VALUE2 = t2.c2.value('@R_VALUE2', 'INT')
    , C_VALUE1 = t3.c3.value('@C_VALUE1', 'INT')
    , C_VALUE2 = t3.c3.value('@C_VALUE2', 'INT')
    , C_VALUE3 = t3.c3.value('@C_VALUE3', 'BIT')
INTO #temp
FROM @x.nodes('survey/section') t(c)
OUTER APPLY t.c.nodes('row') t2(c2)
OUTER APPLY t2.c2.nodes('cell') t3(c3)

INSERT INTO tbl1...
SELECT DISTINCT SE_VALUE1, SE_VALUE2, SE_VALUE3
FROM #temp

INSERT INTO tbl2...
SELECT DISTINCT R_VALUE1, R_VALUE2
FROM #temp t1
JOIN tbl1 t2 ON t1.SE_VALUE1 = t2.SE_VALUE1
WHERE t1.R_VALUE1 IS NOT NULL

INSERT INTO tbl3...
SELECT DISTINCT C_VALUE1, C_VALUE2, C_VALUE3
FROM #temp t1
JOIN tbl2 t2 ON t1.R_VALUE1 = t2.R_VALUE1
WHERE t1.C_VALUE1 IS NOT NULL

Update:

DECLARE @x XML = '
<section SE_VALUE1="34">
    <row R_VALUE1="29">
        <cell C_VALUE1="43830582" />
    </row>
    <row R_VALUE1="30" R_VALUE2="7444255" />
</section>
<section SE_VALUE1="35" />'

SELECT
      SE_VALUE1 = t.c.value('@SE_VALUE1', 'INT')
    , R_VALUE1 = t2.c2.value('@R_VALUE1', 'INT')
    , C_VALUE1 = t3.c3.value('@C_VALUE1', 'INT')
FROM @x.nodes('section') t(c)
OUTER APPLY t.c.nodes('row') t2(c2)
OUTER APPLY t2.c2.nodes('cell') t3(c3)

SELECT
      SE_VALUE1 = t.c.value('@SE_VALUE1', 'INT')
    , R_VALUE1 = t2.c2.value('@R_VALUE1', 'INT')
    , C_VALUE1 = t3.c3.value('@C_VALUE1', 'INT')
FROM @x.nodes('section') t(c)
CROSS APPLY t.c.nodes('row') t2(c2)
CROSS APPLY t2.c2.nodes('cell') t3(c3)

Output:

SE_VALUE1   R_VALUE1    C_VALUE1
----------- ----------- -----------
34          29          43830582
34          30          NULL
35          NULL        NULL

SE_VALUE1   R_VALUE1    C_VALUE1
----------- ----------- -----------
34          29          43830582

edited Mar 15, 2016 at 10:04

answered Mar 15, 2016 at 9:17

Devart

120k23 gold badges172 silver badges189 bronze badges

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

6 Comments

Gottfried Lesigang Over a year ago

You were faster than me :-) Like it!

TheChampp Over a year ago

Ok thank you, but one question. Why do you use outer apply here ?

Devart Over a year ago

@TheChampp because row and cell elements may be missing. I'll add an example

TheChampp Over a year ago

Yes I understand now. When I insert section I need the scope_indentity() to play role as FK when I insert row for this section. Can you advise me some approach ? Should I make it with cursor or ?

Devart Over a year ago

For your tables PK is a INT IDENTITY?

|

Collectives™ on Stack Overflow

SQL Insert From XML Into Table

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related