Geek Answers Handbook

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

Suppose I have XML as shown 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 the SQL table below

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

How can I insert records into a navigation table with the above XML?

In the solution below, you can only insert all records with the parent as NULL, but cannot be filed without a parent link. 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 to Mike, I still could not figure out how to match the name and order using the metx openxml property, please assume that the xml structure in the double question and in the msdn link are slightly 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

Recycled a script based on shredding xml recursively into a database

By executing the script below, I get a runtime error when converting the nvarchar 'Home' value to an int data type.

, XML , ?

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;

8-4: 23: 16IS

script, . , , node Navigations Table , Navigations Table, , , , , XML Navigations, , , .

8-423: 37IS

XML (, ) Navigations, node XML

8-5: 1IS

, . coz ParentID NavigationId, , , , , - . ? script , , xml,

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;
+4
1

, -, , , .

ParentID, , XML .

, , , .

SQL Fiddle

MS SQL Server 2008:

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

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;

:

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

More articles:


All Articles