MS SQL XML - Schema for Bulk Load of sub-elements in XML File

Author

Schema for Bulk Load of sub-elements in XML File

Peter Doyle

2005-10-27, 9:25 am

I am trying to create the schema file to bulk load some xml files. I can get
it to work apart from the AddressLine sub-elements. I have tried loads of
different syntax but have pulled out all of my hair and am now completely
bald! Any suggested solutions will be much appreciated.

an example is

<OrderHeader>

<CountryCode>GBP</CountryCode>
<RetailStoreID>998</RetailStoreID>
<WorkstationID>3</WorkstationID>
<SequenceNumber>6</SequenceNumber>
<CreatedDate>2005-10-10</CreatedDate>
<OrderGrossAmount>497</OrderGrossAmount>
<DeliveryMethod>C</DeliveryMethod>
<Name>Sutton, Tony, Mr</Name>
<AddressLines>
<AddressLine>210 Borough High Street</AddressLine>
<AddressLine>Borough</AddressLine>
<AddressLine>Camden</AddressLine>
<AddressLine>West Yorks</AddressLine>
<AddressLine>London</AddressLine>
</AddressLines>
<PostalCode>TS11TS</PostalCode>
<Email>tony@ottakars.co.uk</Email>

</OrderHeader>
--

Thanks
Peter Doyle

Peter Doyle

2005-10-27, 9:25 am

I am also trying to avoid having to use 2 tables for the import.

--
Peter Doyle

"Peter Doyle" wrote:

> I am trying to create the schema file to bulk load some xml files. I can get
> it to work apart from the AddressLine sub-elements. I have tried loads of
> different syntax but have pulled out all of my hair and am now completely
> bald! Any suggested solutions will be much appreciated.
>
> an example is
>
> <OrderHeader>
>
> <CountryCode>GBP</CountryCode>
> <RetailStoreID>998</RetailStoreID>
> <WorkstationID>3</WorkstationID>
> <SequenceNumber>6</SequenceNumber>
> <CreatedDate>2005-10-10</CreatedDate>
> <OrderGrossAmount>497</OrderGrossAmount>
> <DeliveryMethod>C</DeliveryMethod>
> <Name>Sutton, Tony, Mr</Name>
> <AddressLines>
> <AddressLine>210 Borough High Street</AddressLine>
> <AddressLine>Borough</AddressLine>
> <AddressLine>Camden</AddressLine>
> <AddressLine>West Yorks</AddressLine>
> <AddressLine>London</AddressLine>
> </AddressLines>
> <PostalCode>TS11TS</PostalCode>
> <Email>tony@ottakars.co.uk</Email>
>
> </OrderHeader>
> --
>
> Thanks
> Peter Doyle

Bertan ARI [MSFT]

2005-10-27, 9:26 am

Could you please describe how your tables looks like?

Do you want to load the below Xml into one table with one row for every
address line while the other columns repeat the same value? If so, this is
unfortunately not possible in Bulkload.

You have to create 2 table one for OrderHeader and one for AddressLine.

--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.

"Peter Doyle" < PeterDoyle@discussio
ns.microsoft.com> wrote in message
news:2A6D3925-6FB1-4A66-AAA8- E8CA38EF89FF@microso
ft.com...[color=darkred]
>I am also trying to avoid having to use 2 tables for the import.
>
> --
> Peter Doyle
>
>
> "Peter Doyle" wrote:
>

Peter Doyle

2005-10-27, 9:26 am

Thanks Bertan

I did want to get everything into 1 table and use fields names like
address1,address2 etc. But its very helpful that you have told me it is not
possible so I will use a work around or perhaps 2 tables.

Peter

--
Peter Doyle

"Bertan ARI [MSFT]" wrote:

> Could you please describe how your tables looks like?
>
> Do you want to load the below Xml into one table with one row for every
> address line while the other columns repeat the same value? If so, this is
> unfortunately not possible in Bulkload.
>
> You have to create 2 table one for OrderHeader and one for AddressLine.
>
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
> "Peter Doyle" < PeterDoyle@discussio
ns.microsoft.com> wrote in message
> news:2A6D3925-6FB1-4A66-AAA8- E8CA38EF89FF@microso
ft.com...
>
>
>

Sponsored Links