Insert rows into table in sql server from xml using its nodes and subnodes

Question

Suppose if I have an XML as below

<navigations>
    <navigation>
       <name>Home</name>
       <order>1</order>
    </navigation>
    <navigation>
        <name>Sports</name>
        <order>2</order>
        <subnavigations>
            <navigation>
                <name>Basketball</name>
                <order>1</order>
            </navigation>
            <navigation>
                <name>Cricket</name>
                <order>2</order>
            </navigation>
        </subnavigations>
    <navigation/>
</navigations>

And SQL Table as below

Navigation(
    NavigationId INT PRIMARY_KEY, 
    Name NVARCHAR(128), Order INT, 
    ParentId INT NULL)

How can I insert records into Navigation Table with above XML as input?

The below solution can only insert all records with Parent as NULL but that cannot be served without parent reference. Any ideas?

CREATE PROCEDURE usp_InsertNavigationsFromXML
                            @xmldoc XML
AS
BEGIN
INSERT INTO Navigations(SequenceOrder, Name)
    SELECT
       Col.value('order[1]', 'int'),  
       Col.value('name[1]', 'nvarchar(100)')
    FROM   @xmldoc.nodes('//navigation') Tab(Col)  END
GO

Thanks Mike, I still could not figure out how to map name and order with openxml meta property, please suggest, the xml structure in dup question and in msdn link are little different

merge into Navigations as N
using ( 
      select *
      from openxml(@D, '//*') with 
        (
          ID int '@mp:id',
          ParentNavigationId int '@mp:parentid',
          NavVal nvarchar(128) 'text()',
          SequenceOrder int 'WHAT SHOULD BE MAPPED HERE'
        )
      ) as S

UPDATE 8-4-2239IS

Reworked the script based on shredding xml recursively into the database

While executing below script I am getting runtime error Conversion failed when converting the nvarchar value 'Home' to data type int.

Looks like there is some mapping issue with XML from existing Table, can you suggest?

DECLARE @PublicationId INT
SET @PublicationId = 1
DECLARE @xmldoc XML = '<navigations>
    <navigation>
       <name>Home</name>
       <order>1</order>
    </navigation>
    <navigation>
        <name>Sports</name>
        <order>2</order>
        <subnavigations>
            <navigation>
                <name>Basketball</name>
                <order>1</order>
            </navigation>
            <navigation>
                <name>Cricket</name>
                <order>2</order>
            </navigation>
        </subnavigations>
    </navigation>
</navigations>';

-- OpenXML handle
declare @D int;

-- Table that capture outputof merge with mapping between 
-- DOM node id and the identity column elementID in Element 
declare @T table
(
  ID int,
  ParentNavigationId int,
  NavigationId INT
);

-- Parse XML and get a handle
exec sp_xml_preparedocument @D output, @xmldoc;

-- Add rows to Element and fill the mapping table @T
merge into Navigations as N
using (
      select *
      from openxml(@D, '//*') with 
        (
          ID int '@mp:id',
          ParentID int '@mp:parentid',
          NavValue nvarchar(128) 'text()',
          SequenceOrder int 'text()'
        )
      ) as S
on 0 = 1
when not matched by target then
  insert (PublicationId, NavValue,SequenceOrder) values (@PublicationId, S.NavValue, S.SequenceOrder)output S.ID, S.ParentID, inserted.NavigationId into @T;

-- Update parentId in Elemet
update N
set ParentNavigationId =  T2.NavigationId
from Navigations as N
  inner join @T as T1
    on N.NavigationId = T1.NavigationId
  inner join @T as T2
    on T1.ParentNavigationId = T2.ID
-- Relase the XML document
   exec sp_xml_removedocument @D;

UPDATE 8-4:23:16IS

OK I have figured out the issue with above script, but still not solution. The issue I guess is with merge pattern as both Navigation node and Navigations Table have not all same fields as in Navigations Table there are some extra non-mandatory fields & maybe merge would expect the same number of fields and also the element names in XML is not exactly the same in Navigations Table which I think is the cause of this issue

UPDATE 8-423:37IS

The issue remained same even after syncing the XML element names(name, order) with the fieldnames in Navigations Table, so could be due to mis match number of Fields in Navigations Table vs Elements under Navigation Node in XML

UPDATE 8-5:1IS

Looks like merge is not feasable solution coz ParentID is referenced to NavigationId which is identity column generated after insert, so the only way I guess is to do with cursor. Any suggestions from here? Below is the latest script after making few changes, this is getting parent id generated by open xml but I need reference to Navigation Id

DECLARE @PublicationId INT
SET @PublicationId = 1
DECLARE @xmldoc XML = '<navigations>
    <navigation>
       <NavValue>Home</NavValue>
       <SequenceOrder>1</SequenceOrder>
    </navigation>
    <navigation>
        <NavValue>Sports</NavValue>
        <SequenceOrder>2</SequenceOrder>
        <subnavigations>
            <navigation>
                <NavValue>Basketball</NavValue>
                <SequenceOrder>1</SequenceOrder>
            </navigation>
            <navigation>
                <NavValue>Cricket</NavValue>
                <SequenceOrder>2</SequenceOrder>
            </navigation>
        </subnavigations>
    </navigation>
</navigations>';

-- OpenXML handle
declare @D int;

-- Table that capture outputof merge with mapping between 
-- DOM node id and the identity column elementID in Element 
declare @T table
(
  ID int,
  ParentNavigationId int,
  NavigationId INT
);

-- Parse XML and get a handle
exec sp_xml_preparedocument @D output, @xmldoc;

-- Add rows to Element and fill the mapping table @T
merge into Navigations as N
using (
      select *
      from openxml(@D, '//navigation') 
      with 
        (
          NavigationId int '@mp:id',
          SequenceOrder int 'SequenceOrder',
          ParentNavigationId int '@mp:parentid',
          NavValue nvarchar(128) 'NavValue'
        )
      ) as S
on 0 = 1
when not matched by target then
  insert (PublicationId, SequenceOrder, ParentNavigationId, NavValue) values (@PublicationId, S.SequenceOrder, S.ParentNavigationId, S.NavValue) 
  output S.NavigationId, S.ParentNavigationId, inserted.NavigationId into @T;

-- Update parentId in Elemet
update N
set ParentNavigationId =  T2.NavigationId
from Navigations as N
  inner join @T as T1
    on N.NavigationId = T1.NavigationId
  inner join @T as T2
    on T1.ParentNavigationId = T2.ID
-- Relase the XML document
   exec sp_xml_removedocument @D;

what is the parent reference above, can you give us an example of how it is inserting now, and what you are expecting? — Surendra
– Surendra, Commented Aug 4, 2014 at 13:57
@SurendraNathGM Parent should reference to NavigationId in Navigation Table. If I use above procedure it will inserts NULL to Parent which is default — Naga
– Naga, Commented Aug 4, 2014 at 14:02
There is no ParentID in the XML in the dupe question. ParentID is generated from the hierarchies in the XML using the meta properties. — Mikael Eriksson
– Mikael Eriksson, Commented Aug 4, 2014 at 14:41
Perhaps there is too much a rewrite to work for your XML. I reopen the question to give others a chance to answer. — Mikael Eriksson
– Mikael Eriksson, Commented Aug 4, 2014 at 14:53
@MikaelEriksson Thanks for clarifying, I still struggling to find a map of nodes (name & order) with open xml, the xml node structure provided in dup question or in msdn link are quite different. Below is what I am trying to fix merge into Navigations as N using ( select * from openxml(@D, '//*') with ( ID int '@mp:id', ParentNavigationId int '@mp:parentid', NavVal nvarchar(128) 'text()', SequenceOrder int '???' ) ) as S — Naga
– Naga, Commented Aug 4, 2014 at 15:16

Mikael Eriksson · Accepted Answer · 2014-08-04 19:50:24Z

I am a little confused about your table structure but anyway here is something that I believe does what you want.

Instead of mapping against a ParentID as in the linked answer you can use merge and output the child XML nodes and insert the child nodes in an extra insert.

If you need this to work for more than two levels it is possible to build the merge in a loop that goes level by level.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table dbo.Navigation
(
  ID int identity primary key,
  ParentID int,
  SequenceOrder int, 
  Name nvarchar(100)
);

Query 1:

declare @input xml = '
<navigations>
    <navigation>
       <name>Home</name>
       <order>1</order>
    </navigation>
    <navigation>
        <name>Sports</name>
        <order>2</order>
        <subnavigations>
            <navigation>
                <name>Basketball</name>
                <order>1</order>
            </navigation>
            <navigation>
                <name>Cricket</name>
                <order>2</order>
            </navigation>
        </subnavigations>
    </navigation>
</navigations>';

declare @T table
(
  ID int,
  Navigation xml
);

merge into dbo.Navigation as N
using ( 
      select N.X.value('(name/text())[1]', 'nvarchar(100)') as Name,
             N.X.value('(order/text())[1]', 'int') as SequenceOrder,
             N.X.query('subnavigations/navigation') as Navigation
      from @input.nodes('/navigations/navigation') as N(X)
      ) as S
on 0 = 1
when not matched by target then
  insert (Name, SequenceOrder) values (S.Name, S.SequenceOrder)
output inserted.ID, S.Navigation into @T;

insert into dbo.Navigation(ParentID, Name, SequenceOrder)
select T.ID,
       N.X.value('(name/text())[1]', 'nvarchar(100)'),
       N.X.value('(order/text())[1]', 'int')
from @T as T
  cross apply T.Navigation.nodes('/navigation') as N(X);

select *
from Navigation;

Results:

| ID | PARENTID | SEQUENCEORDER |       NAME |
|----|----------|---------------|------------|
|  1 |   (null) |             1 |       Home |
|  2 |   (null) |             2 |     Sports |
|  3 |        2 |             1 | Basketball |
|  4 |        2 |             2 |    Cricket |

Thanks once again, I couldn't every have achieved this code myself with my scope of understanding, Thanks for your time & attention Sir.

Collectives™ on Stack Overflow

Insert rows into table in sql server from xml using its nodes and subnodes

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related