Вставить строки в таблицу на сервере 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 в качестве входных данных?

Приведенное ниже решение может вставлять только все записи с Parent как 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;