Массовая загрузка XML, относящаяся к автоинкременту родительского идентификатора

Вкратце: я хочу выполнить массовую загрузку XML в базу данных SQL Server (2008) и сгенерировать идентификаторы автоинкремента для родительского элемента, которые можно использовать в дочернем элементе. Это кажется ограниченным объемом: родительский узел не закончен, поэтому еще не вставлен. Кто-нибудь знает способ обойти это?

Более подробное описание (извините, оно действительно длинное, но я стараюсь быть полным):

От заказчика я получил много XML-документов с похожей структурой, из которых можно сгенерировать тестовую БД. Они экспортируются для использования другим инструментом, мой клиент не имеет полномочий или контактов, чтобы влиять на структуру или содержимое. (Инструменты были написаны другой стороной для материнской компании.) У него также нет формального описания XML или базы данных, из которой они экспортируются.

Оказывается, что «верхние» XML-узлы <Registration> действительно имеют идентификаторы, но они не уникальны для разных документов. (Верхние узлы относительны, у них есть корневой узел и узел-список, но в XML они являются наивысшим элементом, который попадет в базу данных.) Идентификаторы могут использоваться в других XML-документах, поскольку они относятся к к другому объекту <Case>, которого нет в экспорте. Поэтому мне нужно сгенерировать auto-increment-id, чтобы все <Registration>-элементы были уникальными даже для разных файлов.

У моего <Registration>-узла много дочерей, например <Activity>-узел. Эти узлы должны ссылаться на своих родителей, поэтому они должны использовать сгенерированный идентификатор автоинкремента. Однако, поскольку они являются частью незавершенного родительского узла, родительский узел все еще находится в области видимости и еще не вставлен в таблицу, как описано в разделе «Подмножество записей и правило упорядочивания ключей» на msdn и technet. Однако примеры на этих сайтах имеют явный уникальный CustomerId, а не автоматически сгенерированный идентификатор.

Хотя эта документация о «Правиле упорядочивания ключей» делает вид, будто это невозможно сделать, я не могу поверить, что нет никакого способа обойти это для XML-файлов, у которых отсутствуют (уникальные) идентификаторы. Еще более странно то, что он вставляет родительский идентификатор в дочерний элемент, но число на единицу меньше. Поэтому я предполагаю, что это идентификатор auto-increment-id из предыдущей области (где 0 - значение по умолчанию, пока ничего не вставлено, я ожидал NULL). Итак, я вижу одно решение: впоследствии увеличивать родительский ключ в моей дочерней таблице (UPDATE Activity SET RegistrationId = RegistrationId + 1). Однако для этого требуется соблюдение лимита (WHERE TimeStamp > ...) и никаких других вмешательств (вручную или сценариев).

Я перепробовал множество различных сценариев отношений и VB-скриптов (например, я бы предпочел автоматическое создание моих таблиц), но я просто опубликую свою последнюю попытку. Это также будет служить для иллюстрации вставки идентификатора автоинкремента из предыдущей области.

Моя основная проблема:

  • возможно ли получить правильный автоматически увеличивающийся родительский идентификатор?

Но приветствуются и другие советы, например:

  • какой параметр использовать для автоматического создания идентификатора автоинкремента без явного CREATE TABLE-оператора в SQL?

Сгенерируйте таблицы:

CREATE TABLE [dbo].[Registration](
  [Id] INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Registration PRIMARY KEY,
  [XmlId] [nvarchar](40) NULL,
)
CREATE TABLE [dbo].[Activity](
  [Id] INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Activity PRIMARY KEY,
  [RegistrationId] INT CONSTRAINT FK_Activity_Registration FOREIGN KEY (RegistrationId) REFERENCES Registration (Id),
  [XmlId] [nvarchar](1000) NULL,
)

XML-файл для импорта:

<Updates>
  <Registrations>
    <Registration ID="NonUniqCaseId-123">
      <Activities>
        <Activity ID="UniqActId-1234" />
        <Activity ID="UniqActId-1235" />
      </Activities>
    </Registration>
    <Registration ID="NonUniqCaseId-124">
      <Activities>
        <Activity ID="UniqActId-1241" />
        <Activity ID="UniqActId-1242" />
      </Activities>
    </Registration>
  </Registrations>
</Updates>

VB-скрипт для проверки загрузки (позже я хочу включить цикл в программу для обработки нескольких файлов):

    Dim objBL 
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=Test;integrated security=SSPI"
objBL.ErrorLogFile = "error.log"

objBL.CheckConstraints = False
objBL.XMLFragment = False
objBL.SchemaGen = True
objBL.SGDropTables = False
objBL.KeepIdentity = False

objBL.Execute "BulkTestMapping.xsd", "BulkTestContents.xml"
Set objBL = Nothing

XSD:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
    attributeFormDefault="qualified"
    elementFormDefault="qualified"
    xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

  <xs:annotation>
    <xs:appinfo>
      <sql:relationship name="Registration_Activity"
            parent="Registration"
            parent-key="Id"
            child="Activity"
            child-key="RegistrationId"
            inverse="true"
            />
    </xs:appinfo>
  </xs:annotation>

  <xs:element name="Registration"
              sql:relation="Registration"
              sql:key-fields="Id" 
            >
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Activities" minOccurs="0" maxOccurs="unbounded" sql:is-constant="true">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Activity" minOccurs="0" maxOccurs="unbounded"
                     sql:relation="Activity" 
                     sql:key-fields="RegistrationId"
                     sql:relationship="Registration_Activity"
              >
                <xs:complexType>
                  <xs:attribute name="ID" sql:field="XmlId" form="unqualified" type="xs:string" />
                  <xs:attribute name="DbId" sql:identity="ignore" sql:field="Id" msdata:AutoIncrement="true" msdata:ReadOnly="true" type="xs:int" /> 
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="ID" form="unqualified" sql:field="XmlId" />
      <xs:attribute name="DbId" sql:identity="ignore" sql:field="Id" msdata:AutoIncrement="true" type="xs:int" /> 
    </xs:complexType>
  </xs:element>
</xs:schema>

Итоговые таблицы (обратите внимание, что RegistrationId отключен на единицу):

[Registration]
Id  XmlId
1   NonUniqCaseId-123
2   NonUniqCaseId-124

[Activity]
Id  RegistrationId  XmlId
1   0   UniqActId-1234
2   0   UniqActId-1235
3   1   UniqActId-1241
4   1   UniqActId-1242

Изменить: это даже хуже, чем я думал. Если я снова добавлю записи, внешний ключ (дочерний ключ) снова начнется с 0! Таким образом, будет сложно или невозможно определить, какой должна быть поправка (по таблице):

[Registration]
Id  XmlId
1   NonUniqCaseId-123
2   NonUniqCaseId-124
3   NonUniqCaseId-123
4   NonUniqCaseId-124

[Activity]
Id  RegistrationId  XmlId
1   0   UniqActId-1234
2   0   UniqActId-1235
3   1   UniqActId-1241
4   1   UniqActId-1242
5   0   UniqActId-1234
6   0   UniqActId-1235
7   1   UniqActId-1241
8   1   UniqActId-1242

sql-server xsd bulk-load sqlxml
person Yahoo Serious    schedule 15.11.2012    source источник
comment
На какой версии SQL Server вы работаете?   -  person Mikael Eriksson    schedule 26.11.2012
comment
SELECT @@version возвращает Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)   -  person Yahoo Serious    schedule 26.11.2012

Ответы (2)


arrow_upward
0
arrow_downward

Я не знаю массовую загрузку с XML, поэтому вот ответ, как сделать это с помощью TSQL.

В SQL Server 2008 вы можете использовать слияние в сочетании с выводом для создания сопоставлений между исходными данными и целевыми автоматически сгенерированными идентификаторами.

Использование merge..output для получения сопоставления между source.id и target.id

Доктор. ВЫВОД или: Как я научился перестать беспокоиться и полюбить Слияние

В этом случае вы можете выполнить слияние с Registration и вывести дочерние узлы XML с сгенерированным идентификатором во временную таблицу или табличную переменную, а затем использовать эту таблицу для вставки в Activity.

SQL Fiddle

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

CREATE TABLE [dbo].[Registration](
  [Id] INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Registration PRIMARY KEY,
  [XmlId] [nvarchar](40) NULL,
);

CREATE TABLE [dbo].[Activity](
  [Id] INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Activity PRIMARY KEY,
  [RegistrationId] INT CONSTRAINT FK_Activity_Registration FOREIGN KEY (RegistrationId) REFERENCES Registration (Id),
  [XmlId] [nvarchar](1000) NULL,
);

Запрос 1:

declare @XML xml = '
<Updates>
  <Registrations>
    <Registration ID="NonUniqCaseId-123">
      <Activities>
        <Activity ID="UniqActId-1234" />
        <Activity ID="UniqActId-1235" />
      </Activities>
    </Registration>
    <Registration ID="NonUniqCaseId-124">
      <Activities>
        <Activity ID="UniqActId-1241" />
        <Activity ID="UniqActId-1242" />
      </Activities>
    </Registration>
  </Registrations>
</Updates>';

declare @T table
(
  RegistrationId nvarchar(40),
  Activities xml
);

merge Registration as T
using
  (
  select R.N.value('@ID', 'nvarchar(40)') as XmlId,
         R.N.query('Activities') as Activities
  from @XML.nodes('/Updates/Registrations/Registration')  as R(N)
  ) as S
on 0 = 1
when not matched then
  insert(XmlId) values (S.XmlId)
output inserted.Id, S.Activities into  @T(RegistrationId, Activities);

insert into Activity(RegistrationId, XmlId)
select T.RegistrationId,
       A.N.value('@ID', 'nvarchar(1000)')
from @T as T
  cross apply T.Activities.nodes('Activities/Activity') as A(N);

Запрос 2:

select *
from Registration;

Результаты:

| ID |             XMLID |
--------------------------
|  1 | NonUniqCaseId-123 |
|  2 | NonUniqCaseId-124 |

Запрос 3:

select *
from Activity;

Результаты:

| ID | REGISTRATIONID |          XMLID |
----------------------------------------
|  5 |              1 | UniqActId-1234 |
|  6 |              1 | UniqActId-1235 |
|  7 |              2 | UniqActId-1241 |
|  8 |              2 | UniqActId-1242 |
person Mikael Eriksson    schedule 26.11.2012
comment
Спасибо за альтернативное решение. Поскольку мой пример представляет собой очень короткую выдержку из моего реального XSD (просто для воспроизведения проблемы), он подразумевает написание большого количества запросов и путей, чего я бы предпочел не делать. К счастью, я нашел альтернативное решение! - person Yahoo Serious; 28.11.2012

arrow_upward
0
arrow_downward

Что ж, ответ оказывается очень простым: просто оставьте inverse в XSD, поэтому удалите эту строку:

inverse="true"

Я ввел это, потому что у меня много отношений «многие ко многим». (Мой пример - это очень короткий отрывок, воспроизводящий проблему.) Но кажется, что я представил ее слишком во многих местах.

Предположение: (К сожалению, у меня нет времени исследовать / подтвердить следующую гипотезу.)

Я предполагаю, сейчас, что inverse следует использовать только для той стороны, которая является дочерью в отношениях, а не со стороны матери. Например. когда A и B имеют отношение «многие ко многим» A_B, и XML выглядит примерно так:

<ListOfA>
  <A ID="Uniq_A123">
    <A_B>
      <B ID="NonUniq_B234" />
    </A_B>
    <A_B>
      <B ID="NonUniq_B235" />
    </A_B>
  </A>
  <A ID="Uniq_A124">
    <A_B>
      <B ID="NonUniq_B234" />
    </A_B>
  </A>
</ListOfA>

A неявно является «родительским» для отношения в A_B, будучи XML-матерью, затем B должен быть явно инвертирован от дочернего к родительскому, указав inverse. Однако, поскольку я генерирую свои собственные идентификаторы для A и B, я сомневаюсь, что это сработает для меня, и я просто буду запускать запросы на восстановление после этого.

person Yahoo Serious    schedule 28.11.2012