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, Aug 04 '14 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, Aug 04 '14 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, Aug 04 '14 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, Aug 04 '14 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, Aug 04 '14 at 15:16
Doesn't looks like it is possible to get a hold of the ParentID using the parent axis so I guess openxml is a no go. Do you want this to work for only two levels or can it be deeper than the example XML? — Mikael Eriksson, Aug 04 '14 at 19:15
@MikaelEriksson, yes thats right getting hold of ParentID cannot be possible with merge I guess. Just 2 levels should be ok for now. You have any better ideas? — Naga, Aug 04 '14 at 19:31
Please do not edit an answer into the question. If you have more to add than the existing answer, you can post your own. — Andrew Barber, Aug 05 '14 at 20:29

score 4 · Accepted Answer · answered Aug 04 '14 at 19:50

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. — Naga, Aug 04 '14 at 20:44

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

1 Answers1