Importing Access DB: xsd

Showing posts with label xsd. Show all posts

Friday, March 30, 2012

importing xml into sql server 7

Is it possible to import an XSD into SQL Server 7 and automagically create
the tables?
Cheers
Iain
None of the SQLXML features work with SQL Server 7. What you are asking for
is available with XML Bulk Load in SQL Server 2000.
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
"Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
news:uK6FKAOpEHA.592@.TK2MSFTNGP11.phx.gbl...
> Is it possible to import an XSD into SQL Server 7 and automagically create
> the tables?
> Cheers
> Iain
>
|||Thanks for the swift reply Roger.
Are there any 3rd party add-ons that you or anyone else know about that
would do the trick?
Here's the situation:
I have XSD documents describing my data (subject to design changes). I wish
to write ASP.NET pages to deal with my data and would prefer to treat it as
xml and use xslt rather than datasets and datagrids. The reason for this is
that my company has some rather funky generic client side scripts that
request the data through http as xml and dynamically build/rebuild the UI -
it saves quite a bit on server load.
However, I'm not convinced that storing the data as purely xml files at the
backend is a very safe or scalable option for a multiuser system. Therefore
I would like to store them in sql server. Also note that I don't have any
data yet as the app is still in design phase :-)
I can easily load them into an XMLDataDocument in .NET, which contains a
dataset object, so it would be relatively easy to use this to interface with
the db (I think that to do this I have to provide the sql to do this through
a data-adapter). However that still leaves the issue of creating the
tables... I either have to do this manually, or else 3rd party tools...
Am I going about this the right way?
Many thanks again
Iain
p.s. is SQLXml available through .NET platform APIs or is it SQL Server? I
know that it uses VB in some way, but I'm a bit hazy on how...
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:ej9oJVOpEHA.800@.TK2MSFTNGP14.phx.gbl...
> None of the SQLXML features work with SQL Server 7. What you are asking
for
> is available with XML Bulk Load in SQL Server 2000.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
> news:uK6FKAOpEHA.592@.TK2MSFTNGP11.phx.gbl...
create
>
|||Bulkload offers SchemaGen option to create tables automatically on the
server based on your AXSD. You may use Bulkload in VB script, any language
supports COM or .Net languages via COM interrupts.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
news:eOJl9jOpEHA.3252@.TK2MSFTNGP14.phx.gbl...
> Thanks for the swift reply Roger.
> Are there any 3rd party add-ons that you or anyone else know about that
> would do the trick?
> Here's the situation:
> I have XSD documents describing my data (subject to design changes). I
wish
> to write ASP.NET pages to deal with my data and would prefer to treat it
as
> xml and use xslt rather than datasets and datagrids. The reason for this
is
> that my company has some rather funky generic client side scripts that
> request the data through http as xml and dynamically build/rebuild the
UI -
> it saves quite a bit on server load.
> However, I'm not convinced that storing the data as purely xml files at
the
> backend is a very safe or scalable option for a multiuser system.
Therefore
> I would like to store them in sql server. Also note that I don't have any
> data yet as the app is still in design phase :-)
> I can easily load them into an XMLDataDocument in .NET, which contains a
> dataset object, so it would be relatively easy to use this to interface
with
> the db (I think that to do this I have to provide the sql to do this
through
> a data-adapter). However that still leaves the issue of creating the
> tables... I either have to do this manually, or else 3rd party tools...
> Am I going about this the right way?
> Many thanks again
> Iain
> p.s. is SQLXml available through .NET platform APIs or is it SQL Server?
I
> know that it uses VB in some way, but I'm a bit hazy on how...
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:ej9oJVOpEHA.800@.TK2MSFTNGP14.phx.gbl...
> for
> rights.
> create
>
|||Thanks for the reply Bertran...
Is bulkload not sql server 2000 only though?
I thought it was part of sqlxml.
Let me know if I'm wrong.
Cheers
Iain
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:%238Spb1ZpEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Bulkload offers SchemaGen option to create tables automatically on the
> server based on your AXSD. You may use Bulkload in VB script, any language
> supports COM or .Net languages via COM interrupts.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
>
> "Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
> news:eOJl9jOpEHA.3252@.TK2MSFTNGP14.phx.gbl...
> wish
> as
this[vbcol=seagreen]
> is
> UI -
> the
> Therefore
any[vbcol=seagreen]
> with
> through
> I
asking
>
|||Yes. But the generated statements could be run on a 7.0 DB if needed.
XML Spy may be a non-Microsoft tool that may have such functionality (just a
guess).
Best regards
Michael
"Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
news:u5woV8ZpEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Thanks for the reply Bertran...
> Is bulkload not sql server 2000 only though?
> I thought it was part of sqlxml.
> Let me know if I'm wrong.
> Cheers
> Iain
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:%238Spb1ZpEHA.1296@.TK2MSFTNGP12.phx.gbl...
> rights.
> this
> any
> asking
>
sql

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.

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.googlegroups.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.googlegroups.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.sql

Friday, March 23, 2012

Importing Problems with XML and XSD into SQL 2000

Im new to everything

I'm tring to import an XML file using the bulkload
method in an vb script within a DTS package. It initally gave me errors of
"relationship expected on 'EventList'" but after some research I specified a
relationship within my xsd file for events with an annotation. However, it
still gives me "the parent/child table of the relationship on 'EventList'
does not match." I think the reason is the parent child relationship is not
necessarly linked by any subelement key. Rather the one of the two "main"
elements in this group "Event" appears in as a "sub"element or referenced
element for the other "main"element "EventList." What code can i use to
establish the SQL:relationship in a way that correctly describes the
elements' relationship? Here's part of the xsd, a little long I apologize:
> <xs:annotation><xs:appinfo><sql:relationship name="Events" parent="Event"
parent-key="Event" child="EventList"
child-key="Event"/></xs:appinfo></xs:annotation><xs:element name="Event"
sql:relation="Event"
sql:relationship="Events"><xs:complexType><xs:sequ ence><xs:element
ref="Action"/><xs:element ref="Date"/><xs:element ref="Time"/><xs:element
ref="SeqNo"/><xs:element ref="ConnID"/><xs:element ref="User"/><xs:element
ref="Code"/><xs:choice><xs:element ref="UserInfo"/><xs:element
ref="EntryInfo"/></xs:choice></xs:sequence><xs:attribute name="ver"
type="xs:string"/></xs:complexType></xs:element><xs:element name="EventList"
sql:relation="EventList"
sql:relationship="Events"><xs:complexType><xs:sequ ence><xs:element
ref="Event" minOccurs="0"
maxOccurs="unbounded"/></xs:sequence></xs:complexType></xs:element>
> thanks kind souls
>
|||Heres some of the xml file. By the way i converted the dtd to an xsd that i posted last time:
<?xml version="1.0" encoding="ISO-8859-1"?><!DOCTYPE LaserFicheAuditTrail SYSTEM "LFAudit.dtd"><LaserFicheAuditTrail Version="2.0"><Database>MBC</Database><EventList><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:35:47</Time><SeqNo>1</SeqNo
><ConnID>1</ConnID><User>ADMIN</User><Code>7331</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:35:54</Time><SeqNo>2</SeqNo><ConnID>2</ConnID><User>ADMIN</User><Code>0</Code><UserInfo
><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGOUT</Action><Date>28-Feb-2004</Date><Time>22:36:17</Time><SeqNo>3</SeqNo><ConnID>2</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGI
N</Action><Date>28-Feb-2004</Date><Time>22:37:05</Time><SeqNo>4</SeqNo><ConnID>3</ConnID><User>ADMIN</User><Code>7331</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:37:11</Time><SeqN
o>5</SeqNo><ConnID>4</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>VIEW</Action><Date>28-Feb-2004</Date><Time>22:37:14</Time><SeqNo>6</SeqNo><ConnID>4</ConnID><User>ADMIN</User><Code>0</Code><En
tryInfo><EntryName>(1) SCANNED DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>FOLDER</EntryType></EntryInfo></Event><Event><Action>VIEW</Action><Date>28-Feb-2004</Date><Time>22:46:42</Time><SeqNo>7</SeqNo><ConnID>4</ConnID><Use
r>ADMIN</User><Code>0</Code><EntryInfo><EntryName>(1) SCANNED DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>FOLDER</EntryType></EntryInfo></Event>
|||And the database tables?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Mardi Morillo" <mmorillo@.mbcsystems.org> wrote in message
news:1C9D3C7F-C25E-46FC-A35E-2845454D5B5B@.microsoft.com...
> Heres some of the xml file. By the way i converted the dtd to an xsd that
i posted last time:
> <?xml version="1.0" encoding="ISO-8859-1"?><!DOCTYPE LaserFicheAuditTrail
SYSTEM "LFAudit.dtd"><LaserFicheAuditTrail
Version="2.0"><Database>MBC</Database><EventList><Event><Action>LOGIN</Actio
n><Date>28-Feb-2004</Date><Time>22:35:47</Time><SeqNo>1</SeqNo><ConnID>1</Co
nnID><User>ADMIN</User><Code>7331</Code><UserInfo><ConnType>RW</ConnType></U
serInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>2
2:35:54</Time><SeqNo>2</SeqNo><ConnID>2</ConnID><User>ADMIN</User><Code>0</C
ode><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGOU
T</Action><Date>28-Feb-2004</Date><Time>22:36:17</Time><SeqNo>3</SeqNo><Conn
ID>2</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</ConnTyp
e></UserInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><T
ime>22:37:05</Time><SeqNo>4</SeqNo><ConnID>3</ConnID><User>ADMIN</User><Code
>7331</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Acti
on>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:37:11</Time><SeqNo>5</SeqN
o><ConnID>4</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</
ConnType></UserInfo></Event><Event><Action>VIEW</Action><Date>28-Feb-2004</D
ate><Time>22:37:14</Time><SeqNo>6</SeqNo><ConnID>4</ConnID><User>ADMIN</User
><Code>0</Code><EntryInfo><EntryName>(1) SCANNED
DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>F
OLDER</EntryType></EntryInfo></Event><Event><Action>VIEW</Action><Date>28-Fe
b-2004</Date><Time>22:46:42</Time><SeqNo>7</SeqNo><ConnID>4</ConnID><User>AD
MIN</User><Code>0</Code><EntryInfo><EntryName>(1) SCANNED
DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>F
OLDER</EntryType></EntryInfo></Event>
|||CREATE TABLE AUDIT_XML_IMPORT
([Action]VARCHAR(100) NULL,
[AnnotateInfo]VARCHAR(100) NULL,
[AnnotColor]VARCHAR(100) NULL,
[AnnotHeight]VARCHAR(100) NULL,
[AnnotID]VARCHAR(100) NULL,
[AnnotLinked]VARCHAR(100) NULL,
[AnnotStampID]VARCHAR(100) NULL,
[AnnotStampName]VARCHAR(100) NULL,
[AnnotType]VARCHAR(100) NULL,
[AnnotWidth]VARCHAR(100) NULL,
[AnnotX]VARCHAR(100) NULL,
[AnnotY]VARCHAR(100) NULL,
[BackColor]VARCHAR(100) NULL,
[Code]VARCHAR(100) NULL,
[ConnID]VARCHAR(100) NULL,
[ConnType]VARCHAR(100) NULL,
[CopyInfo]VARCHAR(100) NULL,
[CreateInfo]VARCHAR(100) NULL,
[Database]VARCHAR(100) NULL,
[Date]VARCHAR(100) NULL,
[DestVolumeID]VARCHAR(100) NULL,
[DestVolumeName]VARCHAR(100) NULL,
[EntryID]VARCHAR(100) NULL,
[EntryInfo]VARCHAR(100) NULL,
[EntryName]VARCHAR(100) NULL,
[EntryType]VARCHAR(100) NULL,
[Event]VARCHAR(100) NULL,
[EventList]VARCHAR(100) NULL,
[ExportComment]VARCHAR(100) NULL,
[ExportInfo]VARCHAR(100) NULL,
[ExportMethod]VARCHAR(100) NULL,
[ExportPages]VARCHAR(100) NULL,
[ExportReason]VARCHAR(100) NULL,
[FieldAfter]VARCHAR(100) NULL,
[FieldBefore]VARCHAR(100) NULL,
[FieldChange]VARCHAR(100) NULL,
[FieldList]VARCHAR(100) NULL,
[FieldName]VARCHAR(100) NULL,
[FieldsInfo]VARCHAR(100) NULL,
[FieldType]VARCHAR(100) NULL,
[FuzzyParameter]VARCHAR(100) NULL,
[HiliteInfo]VARCHAR(100) NULL,
[HiliteTextInfo]VARCHAR(100) NULL,
[LaserFicheAuditTrail]VARCHAR(100) NULL,
[MigrateInfo]VARCHAR(100) NULL,
[ModifyType]VARCHAR(100) NULL,
[MoveInfo]VARCHAR(100) NULL,
[NewName]VARCHAR(100) NULL,
[NewParentID]VARCHAR(100) NULL,
[NewRefID]VARCHAR(100) NULL,
[NoteInfo]VARCHAR(100) NULL,
[NoteText]VARCHAR(100) NULL,
[NumPages]VARCHAR(100) NULL,
[Page]VARCHAR(100) NULL,
[ParentID]VARCHAR(100) NULL,
[RedactColor]VARCHAR(100) NULL,
[RedactInfo]VARCHAR(100) NULL,
[RedactTextInfo]VARCHAR(100) NULL,
[RenameInfo]VARCHAR(100) NULL,
[SearchInfo]VARCHAR(100) NULL,
[SearchString]VARCHAR(100) NULL,
[SeqNo]VARCHAR(100) NULL,
[SourceID]VARCHAR(100) NULL,
[SourceVolumeID]VARCHAR(100) NULL,
[SourceVolumeName]VARCHAR(100) NULL,
[StampInfo]VARCHAR(100) NULL,
[StampMasterInfo]VARCHAR(100) NULL,
[StampPersonalInfo]VARCHAR(100) NULL,
[StartPage]VARCHAR(100) NULL,
[TemplateID]VARCHAR(100) NULL,
[TemplateName]VARCHAR(100) NULL,
[TextColor]VARCHAR(100) NULL,
[TextEnd]VARCHAR(100) NULL,
[TextStart]VARCHAR(100) NULL,
[Time]VARCHAR(100) NULL,
[User]VARCHAR(100) NULL,
[UserInfo]VARCHAR(100) NULL,
[Version]VARCHAR(100) NULL,
[VolumeID]VARCHAR(100) NULL,
[VolumeName]VARCHAR(100) NULL,)
GO
|||The problem is that you've only got a single table - the relationship you've
declared in the schema is trying to join a table called Event to a table
called EventList - neither of which exist in your database.
I'd suggest that the easiest approach would be to "flatten" the data using
an XSLT before performing the bulk load. See
http://sqlxml.org/faqs.aspx?faq=49.
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Mardi" <mmorillo@.mbcsystems.org> wrote in message
news:ECC157F3-BD9E-4415-A248-8D85E6EE7A7B@.microsoft.com...
> CREATE TABLE AUDIT_XML_IMPORT
> ([Action]VARCHAR(100) NULL,
> [AnnotateInfo]VARCHAR(100) NULL,
> [AnnotColor]VARCHAR(100) NULL,
> [AnnotHeight]VARCHAR(100) NULL,
> [AnnotID]VARCHAR(100) NULL,
> [AnnotLinked]VARCHAR(100) NULL,
> [AnnotStampID]VARCHAR(100) NULL,
> [AnnotStampName]VARCHAR(100) NULL,
> [AnnotType]VARCHAR(100) NULL,
> [AnnotWidth]VARCHAR(100) NULL,
> [AnnotX]VARCHAR(100) NULL,
> [AnnotY]VARCHAR(100) NULL,
> [BackColor]VARCHAR(100) NULL,
> [Code]VARCHAR(100) NULL,
> [ConnID]VARCHAR(100) NULL,
> [ConnType]VARCHAR(100) NULL,
> [CopyInfo]VARCHAR(100) NULL,
> [CreateInfo]VARCHAR(100) NULL,
> [Database]VARCHAR(100) NULL,
> [Date]VARCHAR(100) NULL,
> [DestVolumeID]VARCHAR(100) NULL,
> [DestVolumeName]VARCHAR(100) NULL,
> [EntryID]VARCHAR(100) NULL,
> [EntryInfo]VARCHAR(100) NULL,
> [EntryName]VARCHAR(100) NULL,
> [EntryType]VARCHAR(100) NULL,
> [Event]VARCHAR(100) NULL,
> [EventList]VARCHAR(100) NULL,
> [ExportComment]VARCHAR(100) NULL,
> [ExportInfo]VARCHAR(100) NULL,
> [ExportMethod]VARCHAR(100) NULL,
> [ExportPages]VARCHAR(100) NULL,
> [ExportReason]VARCHAR(100) NULL,
> [FieldAfter]VARCHAR(100) NULL,
> [FieldBefore]VARCHAR(100) NULL,
> [FieldChange]VARCHAR(100) NULL,
> [FieldList]VARCHAR(100) NULL,
> [FieldName]VARCHAR(100) NULL,
> [FieldsInfo]VARCHAR(100) NULL,
> [FieldType]VARCHAR(100) NULL,
> [FuzzyParameter]VARCHAR(100) NULL,
> [HiliteInfo]VARCHAR(100) NULL,
> [HiliteTextInfo]VARCHAR(100) NULL,
> [LaserFicheAuditTrail]VARCHAR(100) NULL,
> [MigrateInfo]VARCHAR(100) NULL,
> [ModifyType]VARCHAR(100) NULL,
> [MoveInfo]VARCHAR(100) NULL,
> [NewName]VARCHAR(100) NULL,
> [NewParentID]VARCHAR(100) NULL,
> [NewRefID]VARCHAR(100) NULL,
> [NoteInfo]VARCHAR(100) NULL,
> [NoteText]VARCHAR(100) NULL,
> [NumPages]VARCHAR(100) NULL,
> [Page]VARCHAR(100) NULL,
> [ParentID]VARCHAR(100) NULL,
> [RedactColor]VARCHAR(100) NULL,
> [RedactInfo]VARCHAR(100) NULL,
> [RedactTextInfo]VARCHAR(100) NULL,
> [RenameInfo]VARCHAR(100) NULL,
> [SearchInfo]VARCHAR(100) NULL,
> [SearchString]VARCHAR(100) NULL,
> [SeqNo]VARCHAR(100) NULL,
> [SourceID]VARCHAR(100) NULL,
> [SourceVolumeID]VARCHAR(100) NULL,
> [SourceVolumeName]VARCHAR(100) NULL,
> [StampInfo]VARCHAR(100) NULL,
> [StampMasterInfo]VARCHAR(100) NULL,
> [StampPersonalInfo]VARCHAR(100) NULL,
> [StartPage]VARCHAR(100) NULL,
> [TemplateID]VARCHAR(100) NULL,
> [TemplateName]VARCHAR(100) NULL,
> [TextColor]VARCHAR(100) NULL,
> [TextEnd]VARCHAR(100) NULL,
> [TextStart]VARCHAR(100) NULL,
> [Time]VARCHAR(100) NULL,
> [User]VARCHAR(100) NULL,
> [UserInfo]VARCHAR(100) NULL,
> [Version]VARCHAR(100) NULL,
> [VolumeID]VARCHAR(100) NULL,
> [VolumeName]VARCHAR(100) NULL,)
> GO
>

Friday, March 9, 2012

Importing Data Into XML Column - UPDATED

Okay...I as actually able to get fairly far in my attmepts to copy data from a SQL Server table to an XML column. Here is the XSD I created:

<xsd:element name="CustomColumns">

<xsd:complexType>

<xsd:complexContent>

<xsd:restriction base="xsd:anyType">

<xsd:element name="CN_CUST_KEY" nillable="true">

<xsd:restriction base="xsd:float" />

</xsdimpleType>

</xsd:element>

<xsd:element name="ITM_SUF_NO" nillable="true">

<xsd:restriction base="xsd:float" />

</xsdimpleType>

</xsd:element>

<xsd:element name="Model" nillable="true">

<xsd:restriction base="xsdtring" />

</xsdimpleType>

</xsd:element>

<xsd:element name="Serial" nillable="true">

<xsd:restriction base="xsdtring" />

</xsdimpleType>

</xsd:element>

<xsd:element name="SaleYear" nillable="true">

<xsd:restriction base="xsd:int" />

</xsdimpleType>

</xsd:element>

<xsd:element name="SaleDate" nillable="true">

<xsd:restriction base="xsd Big Smile ateTime" />

</xsdimpleType>

</xsd:element>

<xsd:element name="prd_itm_no" nillable="true">

<xsd:restriction base="xsd:int" />

</xsdimpleType>

</xsd:element>

</xsdequence>

</xsd:restriction>

</xsd:complexContent>

</xsd:complexType>

</xsd:element>

</xsdchema>

This is the SQL query I'm running against the source table:

INSERT INTO [clmnt]

(

name1,

addr1,

addr2,

city,

[state],

zip5,

zip4,

CustomColumns)

SELECT

LastName,

StreetAddress1,

StreetAddress2,

City,

[State],

Postal5,

Postal4,

(SELECT CN_CUST_KEY,

ITM_SUF_NO,

Model,

Serial,

SaleYear,

SaleDate,

prd_itm_no

FROM TblClass_Customers FOR XML PATH (''), ELEMENTS, ROOT('CustomColumns'), TYPE)

FROM TblClass_Customers;

...and here is the error I'm getting:

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]

From what I can see, the SELECT statement matches up with the XSD I'm using...the datatypes coincide...

Kind of at a loss here...

This is what the contents of the target xml column should look like:

<CN_CUST_KEY>XXXYYYZZZZ</CN_CUST_KEY>
<ITM_SUF_NO>45</ITM_SUF_NO>
<Model>alpha</Model>
<Serial>djdjxh3455skx</Serial>
<SaleYear>2005</SaleYear>
<SaleDate>10/25</SaleDate>
<prd_itm_no>df345f</prd_itm_no>

</CustomColumns>

One thing I did find...if I just run the portion of the query that selects the columns for my XML column, the query runs, but doesn't give the expected results. It concatenates everything into one long string...

<CustomColumns><row><CN_CUST_KEY>1000422</CN_CUST_KEY><ITM_SUF_NO>9</ITM_SUF_NO><Model>11073954200</Model><SaleYear>2003</SaleYear><SaleDate>2003-08-23 00:00:00</SaleDate><prd_itm_no>73954</prd_itm_no></row><row><CN_CUST_KEY>1000812</CN_CUST_KEY><ITM_SUF_NO>13</ITM_SUF_NO><Model>11063932101</Model><Serial>MM2610444</Serial><SaleYear>2002</SaleYear><SaleDate>2002-08-26 00:00:00</SaleDate><prd_itm_no>63932</prd_itm_no></row><row><CN_CUST_KEY>1001610</CN_CUST_KEY><ITM_SUF_NO>14</ITM_SUF_NO><Model>11062952100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-03 00:00:00</SaleDate><prd_itm_no>62952</prd_itm_no></row><row><CN_CUST_KEY>1004682</CN_CUST_KEY><ITM_SUF_NO>15</ITM_SUF_NO><Model>11072932100</Model><Serial>ML140325</Serial><SaleYear>2001</SaleYear><SaleDate>2001-04-21 00:00:00</SaleDate><prd_itm_no>72932</prd_itm_no></row><row><CN_CUST_KEY>1004867</CN_CUST_KEY><ITM_SUF_NO>12</ITM_SUF_NO><Model>11073952200</Model><SaleYear>2003</SaleYear><SaleDate>2003-04-08 00:00:00</SaleDate><prd_itm_no>73952</prd_itm_no></row><row><CN_CUST_KEY>1005117</CN_CUST_KEY><ITM_SUF_NO>7</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-18 00:00:00</SaleDate><prd_itm_no>72972</prd_itm_no></row><row><CN_CUST_KEY>1005320</CN_CUST_KEY><ITM_SUF_NO>27</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2001</SaleYear><SaleDate>2001-08-28 -07-01 00:00:00</SaleDate><prd_itm_no>73942</prd_itm_no></row><row><CN_CUST_KEY>5806620</CN_CUST_KEY><ITM_SUF_NO>2</ITM_SUF_NO><Model>11062952100</Model><Serial>ML4334523</Serial><SaleYear>2002</SaleYear><SaleDate>2002-02-17

It also is inserting the <row> tag as opposed to actually creating an entirely new row. And lastly, the <CustomColumns> tag should encapsulate/wrap each record. When I run the select, every single row is contained within <CustomColumns>...

|||

You should have PK or unique columns in the table tblClass_Customers right? Let's assume it's CustomerID. Try change to this:

.....

FROM TblClass_Customers T1 WHERE T1.CustomerID=T2.CustomerID

FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)

FROM TblClass_Customers T2;

|||

Thanks a million!!!

Do you think you could just real quick explain to me what it is that made your chnges do the trick?

I'm learning from this whole XML experience...

Thanks again!!

|||

Phe:

The SELECT alone works fine, but when coupled with the INSERT it blows up:

Code Snippet

INSERT INTO [clmnt]

(

name1,

addr1,

addr2,

city,

[state],

zip5,

zip4,

CustomColumns

)

SELECT

LastName,

StreetAddress1,

StreetAddress2,

City,

[State],

Postal5,

Postal4,

(SELECT CN_CUST_KEY,

ITM_SUF_NO,

Model,

Serial,

SaleYear,

SaleDate,

prd_itm_no

FROM TblClass_Customers2 T1 WHERE T1.CN_CUST_KEY = T2.CN_CUST_KEY

FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)

FROM TblClass_Customers2 T2

The validation error again:

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]

|||

I figured out hat the issue is, but not sure how to address it...

If a column that I'm using to generate my XML is NULL, the element is ignored...that's causing the XML to fail validation.

How would you tell the query to select the column/element even if there is no value or NULL in the source column?

I know there's the whole ELEMENTS XMISIL directive, but I don't wnat to get all the extra "stuff" it generates...

|||

In your schema, you already set nillable to true for certain columns. SO you can just include XSNIL after the ELEMENT jkey word in the query.

If you don't want to include the element with NULL, change the schema to include minOccurs="0" and remove the nillable attribute.

|||

Phe:

Thanks for the reply....

I've actually tried the XSINIL keyword and saw it did bring in the NULL element. But it also brough along some other stuff - attributes and the header:

Code Snippet

<CN_CUST_KEY>1000422</CN_CUST_KEY>

<ITM_SUF_NO>9</ITM_SUF_NO>

<prd_itm_no>73954</prd_itm_no>

</CustomColumns>

Is there any other way to accomplish this without having to have the attribute and the header there? Or is that the only way? I'm going to assume even if the attribute is there, I would just need to modify my queries to account for that particular attribute...

I've never used XQUERY before, and I've been reading through BOL, but again, it's all Greek to me. How would you formulate a basic query against the XML above. For eample, I just want to select all rows where the <Serial> value is NULL (NIL)...?

Am I making sense...? I hope so!!

Thanks again...

|||

You can check e.g.

Code Snippet

SELECT @.x.query(

'CustomColumns[Serial/@.xsi:nil = true()]'

);

|||

Thanks Martin:

But all that query returned was a NULL - and I'm sure there are at least 1000 rows that have a NULL/NIL element

Last question I swear....!

So what is all the namespace declarations I see in all the examples in BOL? Are those necessary and what purpose do they serve?

For example, I want to return all rows from the CLMNT table where the value of the <serial> element in the CustomColumns XML column is 140325. Here is the query I concocted:

Code Snippet

SELECT clmnt.query('

declare default namespace "http://schemas.microsoft.com/sqlserver/2004/07/EVEN/CustomColumns";

/CustomColumns/Serial

') as Result

FROM [CustomColumns].[Serial]

WHERE Serial = '140325'

I'm sure it's completely way off the mark, but if someone could give some insight that would be great.

Also, does anyone know of some realy good very beginner XQUERY documentation/tutorials? I've been through BOL and frankly it didn't help...

Thanks for tolerating an XML noob...

Importing Data Into XML Column - UPDATED

Okay...I as actually able to get fairly far in my attmepts to copy data from a SQL Server table to an XML column. Here is the XSD I created:

<xsd:element name="CustomColumns">

<xsd:complexType>

<xsd:complexContent>

<xsd:restriction base="xsd:anyType">

<xsd:element name="CN_CUST_KEY" nillable="true">

<xsd:restriction base="xsd:float" />

</xsdimpleType>

</xsd:element>

<xsd:element name="ITM_SUF_NO" nillable="true">

<xsd:restriction base="xsd:float" />

</xsdimpleType>

</xsd:element>

<xsd:element name="Model" nillable="true">

<xsd:restriction base="xsdtring" />

</xsdimpleType>

</xsd:element>

<xsd:element name="Serial" nillable="true">

<xsd:restriction base="xsdtring" />

</xsdimpleType>

</xsd:element>

<xsd:element name="SaleYear" nillable="true">

<xsd:restriction base="xsd:int" />

</xsdimpleType>

</xsd:element>

<xsd:element name="SaleDate" nillable="true">

<xsd:restriction base="xsd Big Smile ateTime" />

</xsdimpleType>

</xsd:element>

<xsd:element name="prd_itm_no" nillable="true">

<xsd:restriction base="xsd:int" />

</xsdimpleType>

</xsd:element>

</xsdequence>

</xsd:restriction>

</xsd:complexContent>

</xsd:complexType>

</xsd:element>

</xsdchema>

This is the SQL query I'm running against the source table:

INSERT INTO [clmnt]

(

name1,

addr1,

addr2,

city,

[state],

zip5,

zip4,

CustomColumns)

SELECT

LastName,

StreetAddress1,

StreetAddress2,

City,

[State],

Postal5,

Postal4,

(SELECT CN_CUST_KEY,

ITM_SUF_NO,

Model,

Serial,

SaleYear,

SaleDate,

prd_itm_no

FROM TblClass_Customers FOR XML PATH (''), ELEMENTS, ROOT('CustomColumns'), TYPE)

FROM TblClass_Customers;

...and here is the error I'm getting:

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]

From what I can see, the SELECT statement matches up with the XSD I'm using...the datatypes coincide...

Kind of at a loss here...

This is what the contents of the target xml column should look like:

<CN_CUST_KEY>XXXYYYZZZZ</CN_CUST_KEY>
<ITM_SUF_NO>45</ITM_SUF_NO>
<Model>alpha</Model>
<Serial>djdjxh3455skx</Serial>
<SaleYear>2005</SaleYear>
<SaleDate>10/25</SaleDate>
<prd_itm_no>df345f</prd_itm_no>

</CustomColumns>

|||

You should have PK or unique columns in the table tblClass_Customers right? Let's assume it's CustomerID. Try change to this:

.....

FROM TblClass_Customers T1 WHERE T1.CustomerID=T2.CustomerID

FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)

FROM TblClass_Customers T2;

|||

Thanks a million!!!

Do you think you could just real quick explain to me what it is that made your chnges do the trick?

I'm learning from this whole XML experience...

Thanks again!!

|||

Phe:

The SELECT alone works fine, but when coupled with the INSERT it blows up:

Code Snippet

INSERT INTO [clmnt]

(

name1,

addr1,

addr2,

city,

[state],

zip5,

zip4,

CustomColumns

)

SELECT

LastName,

StreetAddress1,

StreetAddress2,

City,

[State],

Postal5,

Postal4,

(SELECT CN_CUST_KEY,

ITM_SUF_NO,

Model,

Serial,

SaleYear,

SaleDate,

prd_itm_no

FROM TblClass_Customers2 T1 WHERE T1.CN_CUST_KEY = T2.CN_CUST_KEY

FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)

FROM TblClass_Customers2 T2

The validation error again:

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]

|||

I figured out hat the issue is, but not sure how to address it...

If a column that I'm using to generate my XML is NULL, the element is ignored...that's causing the XML to fail validation.

How would you tell the query to select the column/element even if there is no value or NULL in the source column?

I know there's the whole ELEMENTS XMISIL directive, but I don't wnat to get all the extra "stuff" it generates...

|||

In your schema, you already set nillable to true for certain columns. SO you can just include XSNIL after the ELEMENT jkey word in the query.

If you don't want to include the element with NULL, change the schema to include minOccurs="0" and remove the nillable attribute.

|||

Phe:

Thanks for the reply....

I've actually tried the XSINIL keyword and saw it did bring in the NULL element. But it also brough along some other stuff - attributes and the header:

Code Snippet

<CN_CUST_KEY>1000422</CN_CUST_KEY>

<ITM_SUF_NO>9</ITM_SUF_NO>

<prd_itm_no>73954</prd_itm_no>

</CustomColumns>

Am I making sense...? I hope so!!

Thanks again...

|||

You can check e.g.

Code Snippet

SELECT @.x.query(

'CustomColumns[Serial/@.xsi:nil = true()]'

);

|||

Thanks Martin:

But all that query returned was a NULL - and I'm sure there are at least 1000 rows that have a NULL/NIL element

Last question I swear....!

So what is all the namespace declarations I see in all the examples in BOL? Are those necessary and what purpose do they serve?

For example, I want to return all rows from the CLMNT table where the value of the <serial> element in the CustomColumns XML column is 140325. Here is the query I concocted:

Code Snippet

SELECT clmnt.query('

declare default namespace "http://schemas.microsoft.com/sqlserver/2004/07/EVEN/CustomColumns";

/CustomColumns/Serial

') as Result

FROM [CustomColumns].[Serial]

WHERE Serial = '140325'

I'm sure it's completely way off the mark, but if someone could give some insight that would be great.

Also, does anyone know of some realy good very beginner XQUERY documentation/tutorials? I've been through BOL and frankly it didn't help...

Thanks for tolerating an XML noob...

Friday, March 30, 2012

importing xml into sql server 7

Importing xml document to sql 2000 server using xsd schema.

Importing xml document to sql 2000 server using xsd schema.

Friday, March 23, 2012

Importing Problems with XML and XSD into SQL 2000

Friday, March 9, 2012

Importing Data Into XML Column - UPDATED

Importing Data Into XML Column - UPDATED

Importing Access DB

Blog Archive

About Me