MS SQL XML - very simple bulk load

Author

very simple bulk load

Stu

2005-08-18, 9:41 am

Hi,

im trying to do a bulk load with a relationship which I couldnt get to work
(see other thread) so now ive tried breaking it down into a very simple
single insert.
Im sure what im doing is simple to fix but if you dont know the answer you
can spend ages looking. Im trying to just bulk load an portfolio table with
data to start (also no primary key to make it really easy) with.

All data schemas etc defined below

CREATE TABLE [dbo].[portfolio] (
[pfolioCode] [varchar] (10) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL ,
[pfolioDesc] [varchar] (50) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL ,
[pfolioQty] [decimal](18, 2) NOT NULL
) ON [PRIMARY]
GO

xml data file

<?xml version="1.0" encoding="utf-8"?>
<proposedData xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<portfolio>
<pfolioCode>SEITEST1</pfolioCode>
<pfolioDesc>Test Portfolio 1</pfolioDesc>
<pfolioQty>100.23</pfolioQty>
</portfolio>
<portfolio>
<pfolioCode>SEITEST2</pfolioCode>
<pfolioDesc>Test Portfolio 2</pfolioDesc>
<pfolioQty>10.23</pfolioQty>
</portfolio>
</proposedData>

schema :-

<xs:schema xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="unqualified" attributeFormDefault
="unqualified">
<xs:element name="portfolio" msdata:Locale="en-GB">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="portfolio">
<xs:complexType>
<xs:sequence>
<xs:element name="pfolioCode" type="xs:string"
sql:datatype="nvarchar" />
<xs:element name="pfolioDesc" type="xs:string"
sql:datatype="nvarchar" />
<xs:element name="pfolioQty" type="xs:decimal" sql:datatype="decimal"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

Monica Frintu [MSFT]

2005-08-25, 8:24 pm

Hello,

Here are the problems :
- the data doesn't match the schema
- xs:choice is not supported (although you don't need that in your schema)

Here is the updated schema:
<xs:schema xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="unqualified" attributeFormDefault
="unqualified">
<xs:element name="proposedData" msdata:Locale="en-GB"
sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="portfolio">
<xs:complexType>
<xs:sequence>
<xs:element name="pfolioCode" type="xs:string"
sql:datatype="nvarchar" />
<xs:element name="pfolioDesc" type="xs:string"
sql:datatype="nvarchar" />
<xs:element name="pfolioQty" type="xs:decimal"
sql:datatype="decimal"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

I got the following rows inserted in the table:

pfolioCode pfolioDesc pfolioQty

---------- --------------------------------------------------
--------------------
SEITEST1 Test Portfolio 1 100.23
SEITEST2 Test Portfolio 2 10.23

I hope this helps,
Monica Frintu

-------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Stu

2005-08-30, 7:24 am

Hi Monica,

I tried that but nothing happened ie no errors no insert

"Monica Frintu [MSFT]" wrote:

> Hello,
>
> Here are the problems :
> - the data doesn't match the schema
> - xs:choice is not supported (although you don't need that in your schema)
>
> Here is the updated schema:
> <xs:schema xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
> xmlns:xs="http://www.w3.org/2001/XMLSchema"
> elementFormDefault="unqualified" attributeFormDefault
="unqualified">
> <xs:element name="proposedData" msdata:Locale="en-GB"
> sql:is-constant="true">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="portfolio">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="pfolioCode" type="xs:string"
> sql:datatype="nvarchar" />
> <xs:element name="pfolioDesc" type="xs:string"
> sql:datatype="nvarchar" />
> <xs:element name="pfolioQty" type="xs:decimal"
> sql:datatype="decimal"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
>
> I got the following rows inserted in the table:
>
> pfolioCode pfolioDesc pfolioQty
>
> ---------- --------------------------------------------------
> --------------------
> SEITEST1 Test Portfolio 1 100.23
> SEITEST2 Test Portfolio 2 10.23
>
> I hope this helps,
> Monica Frintu
>
> -------------------------------
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
>

Bertan ARI [MSFT]

2005-08-31, 8:24 pm

Did you use the updated schema from Monica and that still didn't work?
Could you please send all the files you use?

Thanks.

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

"Stu" <Stu@discussions.microsoft.com> wrote in message
news:E31BD585-AD31-4564-B225- ABD6E46189A2@microso
ft.com...[color=darkred]
> Hi Monica,
>
> I tried that but nothing happened ie no errors no insert
>
>
>
> "Monica Frintu [MSFT]" wrote:
>

Stu

2005-09-01, 7:24 am

Hi,

Yes I Did use the new schema.
If you give me an address to sent the files to. Ill send them
otherwise the basic setup is in my first email.
Maybe its to do with the versions of msxml im using etc
Although I did install the latest etc

Stu

"Bertan ARI [MSFT]" wrote:

> Did you use the updated schema from Monica and that still didn't work?
> Could you please send all the files you use?
>
> Thanks.
>
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
> "Stu" <Stu@discussions.microsoft.com> wrote in message
> news:E31BD585-AD31-4564-B225- ABD6E46189A2@microso
ft.com...
>
>
>

Bertan ARI [MSFT]

2005-09-08, 7:23 am

Could you send all the files you use to bertanari at gmail?

Thanks

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

"Stu" <Stu@discussions.microsoft.com> wrote in message
news:10B5BF1E-3BCF-4139-BB42- A6ED2D8C54DD@microso
ft.com...[color=darkred]
> Hi,
>
> Yes I Did use the new schema.
> If you give me an address to sent the files to. Ill send them
> otherwise the basic setup is in my first email.
> Maybe its to do with the versions of msxml im using etc
> Although I did install the latest etc
>
> Stu
>
> "Bertan ARI [MSFT]" wrote:
>

Sponsored Links