Вмъкнете редове в таблица в sql сървър от xml, като използвате неговите възли и подвъзли

Да предположим, че имам 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>

И SQL таблица, както по-долу

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

Как мога да вмъкна записи в навигационната таблица с горния XML като вход?

Решението по-долу може да вмъкне само всички записи с родител като NULL, но това не може да се обслужва без препратка към родител. Някакви идеи?

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

Благодаря, Майк, все още не мога да разбера как да съпоставя име и ред с мета свойство openxml, моля, предложете, xml структурата в dup въпроса и в msdn връзката са малко по-различни

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

АКТУАЛИЗАЦИЯ 8-4-2239IS

Преработен е скриптът въз основа на раздробяване на xml рекурсивно в базата данни

Докато изпълнявам скрипта по-долу, получавам грешка по време на изпълнение Преобразуването е неуспешно при преобразуване на стойността на nvarchar „Home“ в тип данни int.

Изглежда, че има проблем с картографирането на 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

Добре, разбрах проблема с горния скрипт, но все още не е решение. Проблемът, който предполагам, е с модела на сливане, тъй като както възелът за навигация, така и таблицата за навигация нямат едни и същи полета, както в таблицата за навигация, има някои допълнителни незадължителни полета и може би сливането би очаквало същия брой полета, а също и имената на елементите в XML са не е точно същото в навигационната таблица, което според мен е причината за този проблем

АКТУАЛИЗАЦИЯ 8-423:37IS

Проблемът остана същият дори след синхронизиране на имената на XML елемента (име, ред) с имената на полетата в навигационната таблица, така че може да се дължи на несъвпадение на броя на полетата в навигационната таблица спрямо елементите под навигационен възел в XML

АКТУАЛИЗАЦИЯ 8-5:1IS

Изглежда, че сливането не е осъществимо решение, тъй като ParentID се препраща към NavigationId, което е колона за идентичност, генерирана след вмъкване, така че единственият начин предполагам е да се направи с курсора. Някакви предложения от тук? По-долу е най-новият скрипт след няколко промени, това е получаване на родителски идентификатор, генериран от отворен 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;