рекурсивное измельчение xml в базу данных

У меня есть следующие данные XML и таблица элементов.

DECLARE @input XML = '<root>
     <C1>
       <C2>
         <C3>           <C4>data1</C4>       </C3>         
       </C2>
       <C2>
         <C3>data2</C3>
       </C2>
     </C1>
     <D1>
        <D2>data3</D2>
        <D2>data4</D2>
     </D1>
    </root>'

Таблица элементов: (это всего лишь пример, поэтому его можно изменить в соответствии с подходящим решением.)

CREATE TABLE Element (  elementId INT IDENTITY PRIMARY KEY, 
elementName VARCHAR (200) NOT NULL, 
parentId INT,   
data VARCHAR(300) );

Согласно @input корневой элемент является родителем C1 и D1, тогда C1 является родителем C2,...

Какое решение для SQL Server 2012/2014 для кодирования хранимой процедуры с CTE (или любым другим типом объекта SQL) для рекурсивного помещения всех имен элементов в таблицу элементов?

столбец данных заполняется данными, в этом случае элементы C4 и второй C3 и D2 имеют данные, остальные элементы равны нулю.

Я также видел иерархический тип данных, и мне интересно, может ли это помочь решить эту проблему?


xml sql sql-server xquery xquery-sql
person Fred Jand    schedule 19.07.2014    source источник
comment
Считаете ли вы, что HierarchyId может помочь вам в процессе заполнения вашей таблицы, или вы готовы изменить структуру таблицы для использования HierarchyId?   -  person Mikael Eriksson    schedule 20.07.2014
comment
Да, я могу изменить структуру таблицы, чтобы иметь тип HierarchyId, цель состоит в том, чтобы уничтожить XML в базе данных, требуемая структура может быть создана соответственно, текущая таблица Element была первой мыслью, о которой я мог подумать.   -  person Fred Jand    schedule 20.07.2014
comment
Вы можете взглянуть на создание пограничной таблицы с помощью оператора OpenXml. Дополнительная информация здесь: msdn.microsoft.com/en-us/library/ms175160. aspx   -  person Mike    schedule 20.07.2014

Ответы (1)


arrow_upward
8
arrow_downward

С помощью OpenXML вы можете получить табличное представление вашего XML с помощью ID и ParentID, используя метасвойства.

Использование XML-запроса в объединении позволит вам создайте таблицу сопоставления между elementId идентификатора и идентификатор узла DOM из XML.

Последний шаг — использовать таблицу сопоставления для обновления parentId в Element.

скрипт SQL

Настройка схемы MS SQL Server 2008:

CREATE TABLE Element (  elementId INT IDENTITY PRIMARY KEY, 
elementName VARCHAR (200) NOT NULL, 
parentId INT,   
data VARCHAR(300) );

Запрос 1:

declare @input xml = '
<root>
  <C1>
    <C2>
      <C3>
        <C4>data1</C4>
      </C3>
    </C2>
    <C2>
      <C3>data2</C3>
    </C2>
  </C1>
  <D1>
    <D2>data3</D2>
    <D2>data4</D2>
  </D1>
</root>';

-- OpenXML handle
declare @D int;

-- Table that capture output of merge with mapping between 
-- DOM node id and the identity column elementID in Element 
declare @T table
(
  ID int,
  ParentID int,
  ElementID int
);

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

-- Add rows to Element and fill the mapping table @T
merge into dbo.Element as E
using ( 
      select *
      from openxml(@D, '//*') with 
        (
          ID int '@mp:id',
          ParentID int '@mp:parentid',
          Data varchar(300) 'text()',
          ElementName varchar(200) '@mp:localname'
        )
      ) as S
on 0 = 1
when not matched by target then
  insert (elementName, data) values (S.ElementName, S.data)
output S.ID, S.ParentID, inserted.elementID into @T;

-- Update parentId in Elemet
update E
set parentId =  T2.ElementID
from dbo.Element as E
  inner join @T as T1
    on E.elementId = T1.ElementID
  inner join @T as T2
    on T1.ParentID = T2.ID


-- Relase the XML document
exec sp_xml_removedocument @D;

select *
from Element;

Результаты:

| ELEMENTID | ELEMENTNAME | PARENTID |   DATA |
|-----------|-------------|----------|--------|
|         1 |        root |   (null) | (null) |
|         2 |          C1 |        1 | (null) |
|         3 |          C2 |        2 | (null) |
|         4 |          C3 |        3 | (null) |
|         5 |          C4 |        4 |  data1 |
|         6 |          C2 |        2 | (null) |
|         7 |          C3 |        6 |  data2 |
|         8 |          D1 |        1 | (null) |
|         9 |          D2 |        8 |  data3 |
|        10 |          D2 |        8 |  data4 |
person Mikael Eriksson    schedule 20.07.2014
comment
Спасибо, это работает хорошо, мне было интересно, может ли XQuery сделать что-то подобное, настоящие XML-файлы относительно объемны, имеет ли смысл загружать их так? - person Fred Jand; 20.07.2014
comment
@FredJand Я не вижу проблем с загрузкой XML с использованием этой техники. Если вас беспокоит структура целевой таблицы, я могу только сказать вам, что это зависит от того, как вы собираетесь запрашивать данные и что вы хотите с ними делать. HierarchyID может быть вариантом для вас, или вы можете просто сбросить весь XML в столбец XML по одной строке для каждого XML-документа. XML-индексы и выборочные XML-индексы (SQL Server 2012 с пакетом обновления 1) могут быть полезны, если вам нужно выполнить запрос в XML. Другим вариантом может быть имитация структуры XML в виде таблиц с соединениями. - person Mikael Eriksson; 20.07.2014