locked
XML Parent and Child nodes into tables RRS feed

  • Question

  • Hi,

    I want to move Parent attributes to one table and child attributes to another table and there should be relationship between two tables. Is is possible to generate Id for parent node and Insert into Child table which enforces the relationship. Is it possible to do this in SQL Stored procedure. 

    <Parent ID="1" Name="a" Repeating="No">
    <Child ID="a" Number="1" Mandatory="Yes"></Child>
    <Child ID="b" Number="2" Mandatory="Yes"></Child>
    </Parent>
    <Parent ID="2" Name="b" Repeating="No">
    <Child ID="d" Number="1" Mandatory="Yes"></Child>
    <Child ID="e" Number="2" Mandatory="Yes"></Child>
    </Parent>

    I want my output to be like this

    Parent Table
    Parent_ID   ID   Name    Repeating   
    1                1    a No
    2                2    b No
    Child Table
    Parent_ID   ID   Number Mandatory
    1              a   1     Yes
    1              b 2     Yes
    2              d 1     Yes
    2             e 2     Yes


    Thanks


    • Edited by Vinuthna Tuesday, August 12, 2014 9:16 AM
    Tuesday, August 12, 2014 9:03 AM

Answers

  • Here's an example using MERGE with the OUTPUT clause:

    USE tempdb
    GO
    
    IF OBJECT_ID('tempdb.dbo.child') IS NOT NULL DROP TABLE dbo.child
    IF OBJECT_ID('tempdb.dbo.parent') IS NOT NULL DROP TABLE dbo.parent
    GO
    
    CREATE TABLE parent ( parent_id INT IDENTITY(100,1) PRIMARY KEY, id INT UNIQUE NOT NULL, name VARCHAR(30) NOT NULL, repeating VARCHAR(5) )
    CREATE TABLE dbo.child ( parent_id INT NOT NULL FOREIGN KEY REFERENCES parent(parent_id), id CHAR(1) NOT NULL PRIMARY KEY, number INT NOT NULL, mandatory VARCHAR(5) )
    GO
    
    DECLARE @xml XML 
    
    SET @xml = '<Parent ID="1" Name="a" Repeating="No">
     <Child ID="a" Number="1" Mandatory="Yes"></Child>
     <Child ID="b" Number="2" Mandatory="Yes"></Child>
     </Parent>
     <Parent ID="2" Name="b" Repeating="No">
     <Child ID="d" Number="1" Mandatory="Yes"></Child>
     <Child ID="e" Number="2" Mandatory="Yes"></Child>
    </Parent>'
    
    --SELECT @xml
    
    DECLARE @child TABLE ( parent_id INT UNIQUE NOT NULL, childXML XML )
    
    ;MERGE dbo.parent t 
    USING
    	(
    	SELECT
    		p.c.value('@ID', 'INT') ID,
    		p.c.value('@Name', 'VARCHAR(30)') Name,
    		p.c.value('@Repeating', 'VARCHAR(5)') repeating, 
    		p.c.query('Child') childXML
    	FROM @xml.nodes('Parent') p(c)
    	) s
    	ON 1 = 2
    
    	WHEN NOT MATCHED 
    		THEN INSERT ( id, name, repeating )
    		VALUES ( id, name, repeating )
    
    	OUTPUT inserted.parent_id, s.childXML INTO @child;
    
    
    INSERT INTO dbo.child ( parent_id, ID, number, mandatory )
    SELECT
    	t.parent_id,
    	c.c.value('@ID', 'CHAR(1)') ID,
    	c.c.value('@Number', 'INT') Number,
    	c.c.value('@Mandatory', 'VARCHAR(5)') Mandatory
    FROM @child t
    	CROSS APPLY t.childXML.nodes('Child') c(c)
    GO
    
    
    SELECT *
    FROM dbo.parent
    
    SELECT *
    FROM dbo.child

    • Marked as answer by Elvis Long Tuesday, August 19, 2014 1:15 PM
    Tuesday, August 12, 2014 9:24 PM
    Answerer

All replies

  • Yes possible

    see an example illustration here

    http://visakhm.blogspot.in/2010/04/using-xml-to-batch-load-master-child.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, August 12, 2014 9:06 AM
  • Here's an example using MERGE with the OUTPUT clause:

    USE tempdb
    GO
    
    IF OBJECT_ID('tempdb.dbo.child') IS NOT NULL DROP TABLE dbo.child
    IF OBJECT_ID('tempdb.dbo.parent') IS NOT NULL DROP TABLE dbo.parent
    GO
    
    CREATE TABLE parent ( parent_id INT IDENTITY(100,1) PRIMARY KEY, id INT UNIQUE NOT NULL, name VARCHAR(30) NOT NULL, repeating VARCHAR(5) )
    CREATE TABLE dbo.child ( parent_id INT NOT NULL FOREIGN KEY REFERENCES parent(parent_id), id CHAR(1) NOT NULL PRIMARY KEY, number INT NOT NULL, mandatory VARCHAR(5) )
    GO
    
    DECLARE @xml XML 
    
    SET @xml = '<Parent ID="1" Name="a" Repeating="No">
     <Child ID="a" Number="1" Mandatory="Yes"></Child>
     <Child ID="b" Number="2" Mandatory="Yes"></Child>
     </Parent>
     <Parent ID="2" Name="b" Repeating="No">
     <Child ID="d" Number="1" Mandatory="Yes"></Child>
     <Child ID="e" Number="2" Mandatory="Yes"></Child>
    </Parent>'
    
    --SELECT @xml
    
    DECLARE @child TABLE ( parent_id INT UNIQUE NOT NULL, childXML XML )
    
    ;MERGE dbo.parent t 
    USING
    	(
    	SELECT
    		p.c.value('@ID', 'INT') ID,
    		p.c.value('@Name', 'VARCHAR(30)') Name,
    		p.c.value('@Repeating', 'VARCHAR(5)') repeating, 
    		p.c.query('Child') childXML
    	FROM @xml.nodes('Parent') p(c)
    	) s
    	ON 1 = 2
    
    	WHEN NOT MATCHED 
    		THEN INSERT ( id, name, repeating )
    		VALUES ( id, name, repeating )
    
    	OUTPUT inserted.parent_id, s.childXML INTO @child;
    
    
    INSERT INTO dbo.child ( parent_id, ID, number, mandatory )
    SELECT
    	t.parent_id,
    	c.c.value('@ID', 'CHAR(1)') ID,
    	c.c.value('@Number', 'INT') Number,
    	c.c.value('@Mandatory', 'VARCHAR(5)') Mandatory
    FROM @child t
    	CROSS APPLY t.childXML.nodes('Child') c(c)
    GO
    
    
    SELECT *
    FROM dbo.parent
    
    SELECT *
    FROM dbo.child

    • Marked as answer by Elvis Long Tuesday, August 19, 2014 1:15 PM
    Tuesday, August 12, 2014 9:24 PM
    Answerer
  • Good answer and similar link

    http://tomaslind.net/2014/01/02/using-xquery-to-insert-xml-hierarchy-into-parent-child-tables/

    https://sqlwithmanoj.com/2011/07/13/select-or-query-nodes-in-hierarchial-or-nested-xml/

    • Edited by Sudip_inn Friday, June 19, 2020 5:01 PM
    Friday, June 19, 2020 4:53 PM