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](http://stackoverflow.com/questions/5491056/how-to-import-xml-file-into-mysql-database-table-using-xml-load-function) — , Mar 15 '16 at 08:39

Devart · Accepted Answer · 2016-03-15T10:04:30.103

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

Ok thank you, but one question. Why do you use outer apply here ? — TheChampp, Mar 15 '16 at 09:48
@TheChampp because row and cell elements may be missing. I'll add an example — Devart, Mar 15 '16 at 10:01
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 ? — TheChampp, Mar 15 '16 at 12:08
Yes. PK is a INT IDENTITY. But I want for example when Section is inserted then I should get the inserted section ID(PK). Because row must know to which FK_SECTION belongs to in the insert statement of the row. The same for cell and row. Cell must know to which ROW_ID belongs to. — TheChampp, Mar 15 '16 at 13:15

SQL Insert From XML Into Table

1 Answers1