gpt4 book ai didi

sql-server - 批量加载 XML 引用自动递增的 parent-id

转载 作者:行者123 更新时间:2023-12-04 02:19:01 25 4
gpt4 key购买 nike

简而言之:我想对 SQL Server (2008) 数据库执行 XML 批量加载并为父项生成可在子项中使用的自动增量 ID。这似乎受到范围的限制:父节点尚未完成,因此尚未插入。有人知道解决这个问题的方法吗?

较长的描述(抱歉,真的很长,但我尽量完整):

我从一位客户那里得到了很多具有类似结构的 XML 文档,可以从中生成测试数据库。它们被导出供其他工具使用,我的客户没有权限或联系人来影响结构或内容。 (这些工具是由另一方为母公司编写的。)他也没有对 XML 或它们从中导出的数据库的正式描述。

事实证明,“顶级”XML 节点 <Registration>确实有 ID,但这些在文档中不是唯一的。 (顶级节点是相对的,它们确实有一个根节点和一个列表节点,但在 XML 中它们是将进入数据库的最高元素。)ID 可以在其他 XML 文档中使用,因为它们引用到另一个对象<Case>那不在导出中。所以我需要生成自动增量 ID 以保留所有 <Registration> - 元素即使在文件中也是唯一的。

我的 <Registration> -节点有很多女儿,例如<Activity> -节点。这些节点需要引用它们的父节点,因此它们应该使用生成的自增 ID。然而,由于它们是未完成的父节点的一部分,父节点仍在范围内,并且尚未插入表中,如 msdn 上的“记录子集和键排序规则”中所述。和 technet .但是,这些站点上的示例具有明确的唯一性 CustomerId ,而不是自动生成的 ID。

尽管有关“键排序规则”的文档使它看起来无法完成,但我无法相信对于缺少(唯一)ID 的 XML 文件没有办法解决这个问题。更奇怪的是:它确实在 child 中插入了一个parent-id,但是数字低了一个。所以我假设这是来自先前范​​围的自动增量 ID(其中 0 是默认值,尚未插入任何内容,我确实期望为 NULL)。所以我确实看到了一种解决方法:之后在我的子表中增加父键( UPDATE Activity SET RegistrationId = RegistrationId + 1 )。但是,这确实需要保持限制 ( WHERE TimeStamp > ... ) 并且没有其他(手动或脚本)干预。

我已经尝试了很多不同的关系和 VB 脚本(例如,我更喜欢自动生成我的表),但我只会发布我最近的尝试。这也将用于说明从先前范围插入自动增量 ID。

我的主要问题是:

  • 是否有可能获得正确的自动递增的 parent-id?

但其他提示也非常受欢迎,例如:

  • 在没有显式 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

最佳答案

我不知道使用 XML 进行批量加载,所以这里是使用 TSQL 执行此操作的答案。

在 SQL Server 2008 中,您可以结合使用合并和输出来创建源数据和目标自动生成的 ID 之间的映射。

Using merge..output to get mapping between source.id and target.id

Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE

在这种情况下,您可以合并到 Registration 并将具有生成的 id 的子 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;

Results :

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

查询 3:

select *
from Activity;

Results :

| ID | REGISTRATIONID |          XMLID |
----------------------------------------
| 5 | 1 | UniqActId-1234 |
| 6 | 1 | UniqActId-1235 |
| 7 | 2 | UniqActId-1241 |
| 8 | 2 | UniqActId-1242 |

关于sql-server - 批量加载 XML 引用自动递增的 parent-id,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13397885/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com