Need Help with XML Schema for Bulkload

I've been struggling for days trying to create an XML View/Schema mapped to
SQL Server to import a relatively straightforward XML document into a simple
(only two tables) SQL Server database. The examples I've seen do not seem to
address cases where some XML elements do not contribute to the population of
the database. And I keep getting errors "needs a relationship" even when I
add relationships. I also want SQL Server to handle the identity columns and
so I set KeepIdentity to False.
So, can anyone help or direct me to examples that could help me understand
the issues. I'm Googled out. Thanks.
XML Document
<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>
Target Database Tables
Pregnancies
PregnancyID
FirstName
LastName
DateOfBirth
PregnancyFacts
PregnancyFactID
PregnancyID (FK)
FactDate
FactName
FactValue
I ran across this on the web
(http://www.topxml.com/sqlxml/using_sqlxmladapter_in_dotnet.asp)
"On the negative side Bulk Load also cannot handle nested types where
children require the IDENTITY of the parent as a foreign key."
I think this is exactly what I was trying to do. For each <Pregnancy>,
automatically generate a PregnancyID identity column, and then use that
PregnancyID in all the PregnancyFacts associated with that pregnancy.
I chose SQLXML Bulkload because I am trying to import millions of elements
into a database.
Is the statement above true for v4.0? How else can I import very large XML
files and tag all PregnancyFacts with a single PregnancyID for each
pregnancy?
Thanks for any help and direction.
"Don Miller" <nospam@.nospam.com> wrote in message
news:Oiyb6DynHHA.4412@.TK2MSFTNGP02.phx.gbl...
> I've been struggling for days trying to create an XML View/Schema mapped
> to SQL Server to import a relatively straightforward XML document into a
> simple (only two tables) SQL Server database. The examples I've seen do
> not seem to address cases where some XML elements do not contribute to the
> population of the database. And I keep getting errors "needs a
> relationship" even when I add relationships. I also want SQL Server to
> handle the identity columns and so I set KeepIdentity to False.
> So, can anyone help or direct me to examples that could help me understand
> the issues. I'm Googled out. Thanks.
> XML Document
> --
> <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>
> Target Database Tables
> --
> Pregnancies
> --
> PregnancyID
> FirstName
> LastName
> DateOfBirth
> PregnancyFacts
> --
> PregnancyFactID
> PregnancyID (FK)
> FactDate
> FactName
> FactValue
>
|||Hello,
I came up with the following schema based on your xml data:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://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"/>
</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"/>
<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>
When I bulkload using this schema and the xml data below I got the folloing
inserted in the tables:
PregnancyID FirstName LastName DateOfBirth
-- -- -- --
1 Jane Doe 1985-07-22 00:00:00.000
2 Mary Smith 1985-06-12 00:00:00.000
(2 row(s) affected)
PregnancyFactID PregnancyID FactDate FactName FactValue
-- -- -- -- --
1 1 2007-03-03 00:00:00.000 Eye Color Brown
2 1 2007-06-06 00:00:00.000 Hair Color Brown
3 2 2007-03-03 00:00:00.000 Eye Color Blue
4 2 2007-06-06 00:00:00.000 Hair Color Blonde
(4 row(s) affected)
Identity values will be propagated from the parent to the child tables.
I hope this solves your problem.
Best regards,
Monica Frintu
"Don Miller" wrote:

> I ran across this on the web
> (http://www.topxml.com/sqlxml/using_sqlxmladapter_in_dotnet.asp)
> "On the negative side Bulk Load also cannot handle nested types where
> children require the IDENTITY of the parent as a foreign key."
> I think this is exactly what I was trying to do. For each <Pregnancy>,
> automatically generate a PregnancyID identity column, and then use that
> PregnancyID in all the PregnancyFacts associated with that pregnancy.
> I chose SQLXML Bulkload because I am trying to import millions of elements
> into a database.
> Is the statement above true for v4.0? How else can I import very large XML
> files and tag all PregnancyFacts with a single PregnancyID for each
> pregnancy?
> Thanks for any help and direction.
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:Oiyb6DynHHA.4412@.TK2MSFTNGP02.phx.gbl...
>
>