Propagating identity values in multiple tables using SQLXMLBulkload
Another issues that came up many times on the discussion list : propagating identity values from parent to child when using SQLXMLBulkload. This is a feature that was introduced with SQLXML3 SP2 release and it's also available in SQL Server 2005.
Basically, the way it works is that bulkload identifies the primary key/foreign key relationship described in the schema file.Then, it will first insert the records in the table with the primary key and propagate the identity values generated by the SQL Server to the tables with the foreign key columns.
Here is a demo on how this works (this is an example from the sqlserver.xml newsgroup):
Consider the following database schema:
Pregnancies ------------
PregnancyID
FirstName
LastName
DateOfBirth
PregnancyFacts ----------------
PregnancyFactID
PregnancyID (FK)
FactDate
FactName
FactValue
CREATE TABLE [dbo].[Pregnancies](
[FirstName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateOfBirth] [datetime] NULL,
[PregnancyID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PregnancyFacts](
[PregnancyID] [int] NOT NULL,
[FactDate] [datetime] NULL,
[FactName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FactValue] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FactID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
The user has the following xml data :
<pregnancies>
<pregnancy>
<summary>
<firstName>Jane</firstName>
<lastName>Doe</lastName>
<dob>7/22/85</dob>
</summary>
<facts>
<fact>
<factDate>3/3/07</factDate>
<factName>Eye Color</factName>
<factValue>Brown</factValue>
</fact>
<fact>
<factDate>6/6/07</factDate>
<factName>Hair Color</factName>
<factValue>Brown</factValue>
</fact>
</facts>
</pregnancy>
<pregnancy>
<summary>
<firstName>Mary</firstName>
<lastName>Smith</lastName>
<dob>6/12/85</dob>
</summary>
<facts>
<fact>
<factDate>3/3/07</factDate>
<factName>Eye Color</factName>
<factValue>Blue</factValue>
</fact>
<fact>
<factDate>6/6/07</factDate>
<factName>Hair Color</factName>
<factValue>Blonde</factValue>
</fact>
</facts>
</pregnancy>
</pregnancies>
Here is the annotated XSD schema:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns="https://tempuri.org/XMLSchema.xsd"
xmlns:xs="https://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="PPF"
parent="Pregnancies"
child="PregnancyFacts"
parent-key="PregnancyID"
child-key="PregnancyID"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="pregnancies" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="pregnancy" sql:relation="Pregnancies">
<xs:complexType>
<xs:sequence>
<xs:element name="summary" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="firstName" sql:field="FirstName" type="xs:string"/>
<xs:element name="lastName" sql:field="LastName" type="xs:string"/>
<xs:element name="dob" sql:field="DateOfBirth" type="xs:date" sql:datatype="datetime"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="facts" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="fact" sql:relation="PregnancyFacts" sql:relationship="PPF">
<xs:complexType>
<xs:sequence>
<xs:element name="factDate" sql:field="FactDate" type="xs:date" sql:datatype="datetime"/>
<xs:element name="factName" sql:field="FactName" type="xs:string"/>
<xs:element name="factValue" sql:field="FactValue" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
I use a simple script to run the bulkload:
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=tempdb;integrated security=SSPI"
objBL.KeepIdentity = false
objBL.ErrorLogFile = "error.xml"
objBL.Execute "schema.xml","data.xml"
set objBL=Nothing
Here is the data that got inserted into the tables:
FirstName LastName DateOfBirth PregnancyID
Jane Doe 1985-07-22 00:00:00.000 1
Mary Smith 1985-06-12 00:00:00.000 2
(2 row(s) affected)
PregnancyID FactDate FactName FactValue FactID
1 2007-03-03 00:00:00.000 Eye Color Brown 1
1 2007-06-06 00:00:00.000 Hair Color Brown 2
2 2007-03-03 00:00:00.000 Eye Color Blue 3
2 2007-06-06 00:00:00.000 Hair Color Blonde 4
(4 row(s) affected)
As you see from the above example, the PregnancyID column values were propagated from the parent table Pregnancies to the child table PregnancyFacts.
I hope this info will shed some light to those who are trying to use this feature.
Comments
Anonymous
June 02, 2009
PingBack from http://patiochairsite.info/story.php?id=26509Anonymous
April 22, 2011
I know it's been a long time since this post, but is there a way to have the child record inserted first and then have the identity of the child table propagate to the parent?