Importing xml document to sql 2000 server using xsd schema.

Hi there,
I am trying to import xml documet to sql(2000) table using bulkload and
xsd schma, some how its not working. i change the schema and make is
XRD WITH THE SAME XML DOCUMENT ITS WORK BUT ITS DOESNOT WORK WITH XSD.
I install sqlxml 3.0 in the server as well as in my computer. Does any
one know where is the error.
below is the xsd shema.
==========
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ExportTourComments"
sql:relation="TourCommentsField">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
<xsd:element name="TourCommentOrder" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentFrom" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentTo" sql:field="TourSheetNumber"
type="xsd:DateTime"type:"sql:DateTime"/>
<xsd:element name="TourCommentDescription"
sql:field="TourSheetNumber" type="xsd:string" type:"sql:nvarchar(95)"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
====
WHEN I RUN THE VBS, it give error message:
script: name.vbs
line:5
char:1
error: A name contained as invalid character
code: 80004005
source: Schema mapping
=========
XRD SCHEMA, ITS WORKING
=========
<?xml version="1.0"?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="TourSheetNumber" dt:type="string"/>
<ElementType name="TourCommentOrder" dt:type="Date"
sql:datatype="dateTime" />
<ElementType name="TourCommentFrom" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentTo" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentDescription" dt:type="string"/>
<ElementType name="dataroot" sql:is-constant="1">
<element type ="ExportTourComments"/>
</ElementType>
<ElementType name="ExportTourComments"
sql:relation="TourCommentsField">
<element type="TourSheetNumber" sql:field="TourSheetNumber" />
<element type="TourCommentOrder" sql:field="TourCommentOrder"
/>
<element type="TourCommentFrom" sql:field="TourCommentFrom" />
<element type="TourCommentTo" sql:field="TourCommentTo" />
<element type="TourCommentDescription"
sql:field="TourCommentDescription" />
</ElementType>
</Schema>
XML DAT
======
<?xml version="1.0" encoding="UTF-16"?>
<dataroot>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:44:16</TourCommentOrder>
<TourCommentDescription>over to the next location and cleaned the snow
from around the well. Calgery decided to</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:43:10</TourCommentOrder>
<TourCommentDescription>foggy. Have to wait untill 13:00 to find out if
we can move. While we were waiting we went</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:37:27</TourCommentOrder>
<TourCommentDescription>stay here and rig up. Rig up all equipment to
regulations. Hold safety meeting. Rig up</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:38:49</TourCommentOrder>
<TourCommentTo>1899-12-30 17:30:00</TourCommentTo>
<TourCommentDescription>slickline. Run recorders. Rig out slickline.
Turn well over to the testers. S.D.F.N.</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:42:11</TourCommentOrder>
<TourCommentFrom>1899-12-30 09:30:00</TourCommentFrom>
<TourCommentDescription>Service and start equipment. Hold pre-job
safety meeting. Could not get permits. Too icy
and</TourCommentDescription>
</ExportTourComments>
</dataroot>
Thanks.
In the XSD schema, the second "type" attribute should be "sql:type", and you
need to use "=" instead of ":" to specify the value.
e.g. instead of:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
use:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" sql:type="sql:nvarchar(9)"/>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"SESC-SQLDeveloper@.telus.net" <itimilsina@.savannaenergy.com> wrote in
message news:1112893150.970921.243090@.o13g2000cwo.googlegr oups.com...
Hi there,
I am trying to import xml documet to sql(2000) table using bulkload and
xsd schma, some how its not working. i change the schema and make is
XRD WITH THE SAME XML DOCUMENT ITS WORK BUT ITS DOESNOT WORK WITH XSD.
I install sqlxml 3.0 in the server as well as in my computer. Does any
one know where is the error.
below is the xsd shema.
==========
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ExportTourComments"
sql:relation="TourCommentsField">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
<xsd:element name="TourCommentOrder" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentFrom" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentTo" sql:field="TourSheetNumber"
type="xsd:DateTime"type:"sql:DateTime"/>
<xsd:element name="TourCommentDescription"
sql:field="TourSheetNumber" type="xsd:string" type:"sql:nvarchar(95)"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
====
WHEN I RUN THE VBS, it give error message:
script: name.vbs
line:5
char:1
error: A name contained as invalid character
code: 80004005
source: Schema mapping
=========
XRD SCHEMA, ITS WORKING
=========
<?xml version="1.0"?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="TourSheetNumber" dt:type="string"/>
<ElementType name="TourCommentOrder" dt:type="Date"
sql:datatype="dateTime" />
<ElementType name="TourCommentFrom" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentTo" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentDescription" dt:type="string"/>
<ElementType name="dataroot" sql:is-constant="1">
<element type ="ExportTourComments"/>
</ElementType>
<ElementType name="ExportTourComments"
sql:relation="TourCommentsField">
<element type="TourSheetNumber" sql:field="TourSheetNumber" />
<element type="TourCommentOrder" sql:field="TourCommentOrder"
/>
<element type="TourCommentFrom" sql:field="TourCommentFrom" />
<element type="TourCommentTo" sql:field="TourCommentTo" />
<element type="TourCommentDescription"
sql:field="TourCommentDescription" />
</ElementType>
</Schema>
XML DAT
======
<?xml version="1.0" encoding="UTF-16"?>
<dataroot>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:44:16</TourCommentOrder>
<TourCommentDescription>over to the next location and cleaned the snow
from around the well. Calgery decided to</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:43:10</TourCommentOrder>
<TourCommentDescription>foggy. Have to wait untill 13:00 to find out if
we can move. While we were waiting we went</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:37:27</TourCommentOrder>
<TourCommentDescription>stay here and rig up. Rig up all equipment to
regulations. Hold safety meeting. Rig up</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:38:49</TourCommentOrder>
<TourCommentTo>1899-12-30 17:30:00</TourCommentTo>
<TourCommentDescription>slickline. Run recorders. Rig out slickline.
Turn well over to the testers. S.D.F.N.</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:42:11</TourCommentOrder>
<TourCommentFrom>1899-12-30 09:30:00</TourCommentFrom>
<TourCommentDescription>Service and start equipment. Hold pre-job
safety meeting. Could not get permits. Too icy
and</TourCommentDescription>
</ExportTourComments>
</dataroot>
Thanks.
|||Actually, almost right, it should be sql:datatype. e.g.
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" sql:datatype="nvarchar(9)"/>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:ey7ARQBPFHA.3788@.tk2msftngp13.phx.gbl...
In the XSD schema, the second "type" attribute should be "sql:type", and you
need to use "=" instead of ":" to specify the value.
e.g. instead of:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
use:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" sql:type="sql:nvarchar(9)"/>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"SESC-SQLDeveloper@.telus.net" <itimilsina@.savannaenergy.com> wrote in
message news:1112893150.970921.243090@.o13g2000cwo.googlegr oups.com...
Hi there,
I am trying to import xml documet to sql(2000) table using bulkload and
xsd schma, some how its not working. i change the schema and make is
XRD WITH THE SAME XML DOCUMENT ITS WORK BUT ITS DOESNOT WORK WITH XSD.
I install sqlxml 3.0 in the server as well as in my computer. Does any
one know where is the error.
below is the xsd shema.
==========
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ExportTourComments"
sql:relation="TourCommentsField">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
<xsd:element name="TourCommentOrder" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentFrom" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentTo" sql:field="TourSheetNumber"
type="xsd:DateTime"type:"sql:DateTime"/>
<xsd:element name="TourCommentDescription"
sql:field="TourSheetNumber" type="xsd:string" type:"sql:nvarchar(95)"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
====
WHEN I RUN THE VBS, it give error message:
script: name.vbs
line:5
char:1
error: A name contained as invalid character
code: 80004005
source: Schema mapping
=========
XRD SCHEMA, ITS WORKING
=========
<?xml version="1.0"?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="TourSheetNumber" dt:type="string"/>
<ElementType name="TourCommentOrder" dt:type="Date"
sql:datatype="dateTime" />
<ElementType name="TourCommentFrom" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentTo" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentDescription" dt:type="string"/>
<ElementType name="dataroot" sql:is-constant="1">
<element type ="ExportTourComments"/>
</ElementType>
<ElementType name="ExportTourComments"
sql:relation="TourCommentsField">
<element type="TourSheetNumber" sql:field="TourSheetNumber" />
<element type="TourCommentOrder" sql:field="TourCommentOrder"
/>
<element type="TourCommentFrom" sql:field="TourCommentFrom" />
<element type="TourCommentTo" sql:field="TourCommentTo" />
<element type="TourCommentDescription"
sql:field="TourCommentDescription" />
</ElementType>
</Schema>
XML DAT
======
<?xml version="1.0" encoding="UTF-16"?>
<dataroot>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:44:16</TourCommentOrder>
<TourCommentDescription>over to the next location and cleaned the snow
from around the well. Calgery decided to</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:43:10</TourCommentOrder>
<TourCommentDescription>foggy. Have to wait untill 13:00 to find out if
we can move. While we were waiting we went</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:37:27</TourCommentOrder>
<TourCommentDescription>stay here and rig up. Rig up all equipment to
regulations. Hold safety meeting. Rig up</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:38:49</TourCommentOrder>
<TourCommentTo>1899-12-30 17:30:00</TourCommentTo>
<TourCommentDescription>slickline. Run recorders. Rig out slickline.
Turn well over to the testers. S.D.F.N.</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:42:11</TourCommentOrder>
<TourCommentFrom>1899-12-30 09:30:00</TourCommentFrom>
<TourCommentDescription>Service and start equipment. Hold pre-job
safety meeting. Could not get permits. Too icy
and</TourCommentDescription>
</ExportTourComments>
</dataroot>
Thanks.