error when executing sql xml bulk load

I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
my problem. I get a bulk load error stating I need a sql:relationship for
any of my nested complex types but the elements in my nested complex type is
not going to another table but to the same table as my first complex type.
How do I create nested complex types that bulk load the elements to the same
table?
In the example below how do I represent the "Phone" as a complex type in the
xsd and have the bulk upload load the elements to the same table as my root
complex type?
Ex.
<Root>
<ContactInfo>
<FirstName></FirstName>
<LastName></LastName>
<Phone>
<Home></Home>
<Cell></Cell>
</Phone>
</ContactInfo>
</Root>
So how do i layout the xsd for the "Phone" and either not have to provide
the relationship if the data goes to the same sql table or how do I provide
the sql:relationship so I don't get the error?Hello Semloh,
I have notice that you have send the same issue on Feb 27th which I have
replied. Please check my answer there, and if you need any further
assistance on this particular issue please reply to me in that thread so I
can follow up with you. In the future, please don't cross-post the same
question in multiple newsgroups. This will help our engineers work on your
question more efficiently. Your understanding and cooperation is
appreciated.
For your convenience, I have included my reply as follows:
---
Based on my research, you should follow the article to create 2 table to
get the XML file uploaded to the database.
XML Bulk Load Examples (SQLXML 4.0)
http://msdn2.microsoft.com/en-us/ms171806.aspx
In this article you could see that the XSD schema include a
sql:relationship attribute.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustCustOrder"
parent="Cust"
parent-key="CustomerID"
child="CustOrder"
child-key="CustomerID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="ROOT" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Customers" sql:relation="Cust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" />
<xsd:element name="CompanyName" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
<xsd:element name="Order"
sql:relation="CustOrder"
sql:relationship="CustCustOrder" >
<xsd:complexType>
<xsd:attribute name="OrderID" type="xsd:integer" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
If you want to upload all the element into one table, you could use a XSLT
to transform the original XML file to up the level of your Phone element.
---
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi,
If the Phone element is just a wrapper for Home and Cell (whch will be
mapped to fileds in the database) that you can use sql:is-constant="true" on
the Phone element and you don't need relationship.
I hope this helps.
Thanks,
--
Monica Frintu
"semloh" wrote:

> I am using the SQL bulk load 4 but am new to xsd and sql schemas. Here is
> my problem. I get a bulk load error stating I need a sql:relationship for
> any of my nested complex types but the elements in my nested complex type
is
> not going to another table but to the same table as my first complex type.
> How do I create nested complex types that bulk load the elements to the sa
me
> table?
> In the example below how do I represent the "Phone" as a complex type in t
he
> xsd and have the bulk upload load the elements to the same table as my roo
t
> complex type?
> Ex.
> <Root>
> <ContactInfo>
> <FirstName></FirstName>
> <LastName></LastName>
> <Phone>
> <Home></Home>
> <Cell></Cell>
> </Phone>
> </ContactInfo>
> </Root>
>
> So how do i layout the xsd for the "Phone" and either not have to provide
> the relationship if the data goes to the same sql table or how do I provid
e
> the sql:relationship so I don't get the error?
>