need a XMLBULKLOAD example that fits my data,

[Peter Johnson with AmericanRetailSupply]

I've been studying XMLBULKLOAD for the past 2 weeks, and have succeed with
many of the XML BulkLoad examples, but none of the examples fit my data model.

Here's the classic examples from BOL:
shorthand version...
<PO>
<POHeader>
<element>
<element>
<PODETAILS>
<element line1>
<element line2>
</PODETAILS>
</POHEADER>
</PO>

and from what I see
<annotation> relationship statement here
<PO> <- is-constant="1" to ignore
<POHeader> <-sql:relation pointing to the SQL table
<element> <- elements map to sql if names match table
<element>
<PODETAILS> <-sql:relation AND sql:relationship
<element line1>
<element line2>
</PODETAILS>
</POHEADER>
</PO>

all the above works great.

Here's my data , the way that it is being handed to me:

<PO>
<POHEADER>
<element/>
<element/>
</POHEADER>

<PODETAILS>
<LINE>
<element/>
<element/>
</LINE>
<LINE>
<element/>
<element/>
</LINE>
</PODETAILS>
</PO>

There seems to be a big difference between the Line details being children
of the POHEADER, and being children of a different 'parent'

I've guessed at the sql:relation tags, with no success. I can import the
single header line of information, but none of the PO line details will come
into any SQL table. I can get into the details, but wonder if there is
something fundamentally wrong with what I"m trying to do.
I would be happy for the XML data to come into one big sql table, or 2
seperate sql tables, one for header and one for details.

Peter Johnson
--
American Retail Supply
-giving the gift of better retail management-

Data Conversion, Custom Reporting

We're helping small business owners compete again the Walmarts of their
worlds.

[Peter Johnson with AmericanRetailSupply]

I read another post in this group and saw the following comment:
""Unfortunately, your scenario is currently not supported by Bulkload.
Currently we do not allow attributes on constant elements and there are no
future plans to support it."" Would my data below fit this situation, and
so I need to abandon using bulkload and start over with OPENXML?

here's and example of what I need into SQL, I don't care if it's in one
table or 2
<Order>
<OrderHeader>
<OrderNumber>ABC123</OrderNumber>
<ShipTo>0000</ShipTo>
<OrderDate>20090121</OrderDate>
</OrderHeader>
<OrderDetails>
<Orderline>
<ProductId>WB007IDP*30</ProductId>
<Quantity>1</Quantity>
</Orderline>
<Orderline>
<ProductId>WB007IDP*30</ProductId>
<Quantity>1</Quantity>
</Orderline>
</OrderDetails>
</Order>

--
American Retail Supply
-giving the gift of better retail management-

Data Conversion, Custom Reporting

We''re helping small business owners compete again the Walmarts of their
worlds.

[Bob]

I got this to work, although it seems a bit overcomplicated. It actually
generates four tables when running the bulkload with SchemaGen = True:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<sql:relationship
name="Order_OrderHeader"
parent="Orders"
parent-key="OrderNumber"
child="OrderHeader"
child-key="OrderNumber" />
<sql:relationship
name="Order_OrderDetails"
parent="Orders"
parent-key="OrderNumber"
child="OrderDetails"
child-key="OrderNumber" />
<sql:relationship
name="Order_Orderline"
parent="Orders"
parent-key="OrderNumber"
child="Orderline"
child-key="OrderNumber" />
<sql:relationship
name="OrderHeader_Orderline"
parent="OrderHeader"
parent-key="OrderNumber"
child="Orderline"
child-key="OrderNumber" />
<sql:relationship
name="OrderDetails_Orderline"
parent="OrderDetails"
parent-key="OrderNumber"
child="Orderline"
child-key="OrderNumber" />

</xs:appinfo>
</xs:annotation>

<xs:element name="Order" sql:relation="Orders">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderHeader" sql:relationship="Order_OrderHeader">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderNumber" type="xs:string" />
<xs:element name="ShipTo" type="xs:string" />
<xs:element name="OrderDate" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="OrderDetails" sql:relationship="Order_OrderDetails">
<xs:complexType>
<xs:sequence>
<xs:element name="Orderline"
sql:relationship="OrderDetails_Orderline">
<xs:complexType>
<xs:sequence>
<xs:element name="ProductId" type="xs:string" />
<xs:element name="Quantity" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Hope that helps!