Showing posts with label schema. Show all posts
Showing posts with label schema. Show all posts

Friday, March 30, 2012

Importing XML Sample file and XML Schema

I recently received a schema file from a vendor to import into my SQL Server
.
I am new to XML and have been reading all I can. I tried doing a Bulk Load
in a DTS Package, but it does not read the schema as the correct file format
(asking me ANSI, Unicode, etc). I have a sample data file to test with but
it does not seem to be working. Can anyone give any insight on the easiest
way to do this? I will be receiving a wly feed from this vendor and will
need to import on a schedule. Thanks.Do you need to "shred" it into relational fields or keep the whole XML
document in a single field?
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>I recently received a schema file from a vendor to import into my SQL
>Server.
> I am new to XML and have been reading all I can. I tried doing a Bulk
> Load
> in a DTS Package, but it does not read the schema as the correct file
> format
> (asking me ANSI, Unicode, etc). I have a sample data file to test with
> but
> it does not seem to be working. Can anyone give any insight on the
> easiest
> way to do this? I will be receiving a wly feed from this vendor and
> will
> need to import on a schedule. Thanks.|||I need to shred it. There are multiple tables involved.
"Michael Rys [MSFT]" wrote:

> Do you need to "shred" it into relational fields or keep the whole XML
> document in a single field?
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>
>|||You have a couple of options:
Use the SQLXML 3.0 XML Bulkload component. You will need to add annotations
to your schema (see the documentation).
Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT parameter
(you need to make sure that the XML document is in an encoding compatible
with the SQL Server collation codepage).
Write your own ASP/ASP.Net mid-tier code to perform the shredding.
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...
>I need to shred it. There are multiple tables involved.
> "Michael Rys [MSFT]" wrote:
>|||Hello - found the issue - apparently, the:
targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutionsAx
apta30_2.xsd"
needs to be removed from the <xs:schema> tag.
Now, can anyone tell me why? I'm not an XML expert but learning as much as I
can...
AB
"Michael Rys [MSFT]" wrote:

> You have a couple of options:
> Use the SQLXML 3.0 XML Bulkload component. You will need to add annotation
s
> to your schema (see the documentation).
> Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT paramete
r
> (you need to make sure that the XML document is in an encoding compatible
> with the SQL Server collation codepage).
> Write your own ASP/ASP.Net mid-tier code to perform the shredding.
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...
>
>|||The targetnamespace indicates that the elements need to belong to the
namespace to be matched by the schema definitions.
Best regards
Michael
"Illustris" <Illustris@.discussions.microsoft.com> wrote in message
news:1EC9D995-DADE-4321-B9C4-AC8A55E07E51@.microsoft.com...
> Hello - found the issue - apparently, the:
> targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutions
Axapta30_2.xsd"
> needs to be removed from the <xs:schema> tag.
> Now, can anyone tell me why? I'm not an XML expert but learning as much as
> I
> can...
> AB
> "Michael Rys [MSFT]" wrote:
>

Importing XML Sample file and XML Schema

I recently received a schema file from a vendor to import into my SQL Server.
I am new to XML and have been reading all I can. I tried doing a Bulk Load
in a DTS Package, but it does not read the schema as the correct file format
(asking me ANSI, Unicode, etc). I have a sample data file to test with but
it does not seem to be working. Can anyone give any insight on the easiest
way to do this? I will be receiving a weekly feed from this vendor and will
need to import on a schedule. Thanks.
Do you need to "shred" it into relational fields or keep the whole XML
document in a single field?
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>I recently received a schema file from a vendor to import into my SQL
>Server.
> I am new to XML and have been reading all I can. I tried doing a Bulk
> Load
> in a DTS Package, but it does not read the schema as the correct file
> format
> (asking me ANSI, Unicode, etc). I have a sample data file to test with
> but
> it does not seem to be working. Can anyone give any insight on the
> easiest
> way to do this? I will be receiving a weekly feed from this vendor and
> will
> need to import on a schedule. Thanks.
|||I need to shred it. There are multiple tables involved.
"Michael Rys [MSFT]" wrote:

> Do you need to "shred" it into relational fields or keep the whole XML
> document in a single field?
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>
>
|||You have a couple of options:
Use the SQLXML 3.0 XML Bulkload component. You will need to add annotations
to your schema (see the documentation).
Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT parameter
(you need to make sure that the XML document is in an encoding compatible
with the SQL Server collation codepage).
Write your own ASP/ASP.Net mid-tier code to perform the shredding.
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...[vbcol=seagreen]
>I need to shred it. There are multiple tables involved.
> "Michael Rys [MSFT]" wrote:
|||Hello - found the issue - apparently, the:
targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutionsAxapta30_2.xsd"
needs to be removed from the <xs:schema> tag.
Now, can anyone tell me why? I'm not an XML expert but learning as much as I
can...
AB
"Michael Rys [MSFT]" wrote:

> You have a couple of options:
> Use the SQLXML 3.0 XML Bulkload component. You will need to add annotations
> to your schema (see the documentation).
> Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT parameter
> (you need to make sure that the XML document is in an encoding compatible
> with the SQL Server collation codepage).
> Write your own ASP/ASP.Net mid-tier code to perform the shredding.
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...
>
>
|||The targetnamespace indicates that the elements need to belong to the
namespace to be matched by the schema definitions.
Best regards
Michael
"Illustris" <Illustris@.discussions.microsoft.com> wrote in message
news:1EC9D995-DADE-4321-B9C4-AC8A55E07E51@.microsoft.com...[vbcol=seagreen]
> Hello - found the issue - apparently, the:
> targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutionsAxapta30_2.xsd"
> needs to be removed from the <xs:schema> tag.
> Now, can anyone tell me why? I'm not an XML expert but learning as much as
> I
> can...
> AB
> "Michael Rys [MSFT]" wrote:
sql

Importing XML in SQL Server 2005 in .Net 2.0

Yes, I have schema. I have also created database structure from XML
Schema. XML Schema is in such a way that it is not having annotation
suitlable for SQLXML.
You mentioned about loading XML into tables, but how do I update table
into database (I assume you mean Table datastruction in ADO.net)?
Thanks
NeoHello Neo,
Okay, so let me ask this: are you simply interested in loading the XML into
the database in some batch operation or do you both load the orginal data
into SQL Server and modify stored values from XML?
If it this is a simple ETL (former) case, SSIS would be my tool of choice.
If you're looking to both load and update from XML, SSIS is still usable,
but the solution might look a little different.
If you simply putting the XML into a XML-typed column, consider using openro
wset(bulk)
instead.
More details helpful. :)
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

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

Importing XML data into a SQL Server database

Hello,
I am having a bit of a problem, and maybe someone out there can
help. The Schema file (test_schema.xml) is:
<?xml version="1.0" ?>
<xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="Root">
<xs:complexType>
<xs:sequence>
<xs:element name="Truserv" sql:mapped="false">
<xs:complexType>
<xs:sequence>
<xs:element name="Item" sql:relation="Test_Table">
<xs:complexType>
<xs:attribute name="item_nbr" type="xs:unsignedInt"
use="required" sql:field="Test_Col1" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
The XML file is:
<ROOT>
<TRU_SERV>
<Items test_attribute1="This is a test" />
</TRU_SERV>
</ROOT>
and my vb script code is:
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=aaa;database=xxx;uid=yyy;pwd=zzz"
objBL.ErrorLogFile = "error.log"
objBL.Execute "test_schema.xml", "test_xml.xml"
Set objBL = Nothing
When I run the vb code, the code will execute, but it will not insert
the data into the database. It just runs and from my end, seems to do
nothing. It does connect to the database, since I am not getting any
errors. If anyone out there has any ideas, please let me know.
-Jay
(patel@.cs.utk.edu)
In the XML file one of the tags is called <TRU_SERV>
where as in the schema it is called <xs:element name="Truserv" sql:mapped="false"> These names must match exactly for this to work.
|||also you have the same problem with "item" and "items"

Monday, March 26, 2012

Importing Sqldatabase and Publishing it

HI,

I am working on a project in which a user can upload his database to the server...then he is able to view all the schema or execute any kind of query. Then he goes to some other machine and login and wants to download the database.

For example Alex have uploaded the database consisting of 1000 records to mysite. Then He goes to texas to find some oil wells. He is on another machine ... I download the database ... inseert new records and upload it back.

Its an example basically I want to know how to include the users' uploaded into my sqlserver and when required publish with data on his machine...

Thanks.

I suppose you're thinking about some disconnected scenario where you can upload a scheme, edit some rows, travel to a desert or mountain top, find oil, edit some rows offline and then synchronize the data with the server version. As far as I can see, you'll be needing a shared centralized SQL server and a personal/express edition on (let's say) a laptop. You can easily work offline and synchronize the two machines online either by replication or programmatically through a .NET application.

But since you're talking about a database with 1000 rows, you're thinking more of tables than databases. If this is true, you may consider exporting XML documents (with 1000 rows), edit the XML document (as if it were a real table) and upload the XML document later so the data can be synchronized. This is quite easy and commonly used. You can set up a database (many tables) where your customers can up- and download XML documents. You can use ASP.NET or SQL2005 to do the translation from XML to native data and back.

Greetings,

Robert

|||

Basically i am creating a web site where you will upload your database and then all the queries will be executed their.

Problem is your Databse should now be attached to my SQL Server. Attaching it programmatically is the basic problem right now. and then next when you want to download it to your local machine detaching it and publishing with data is my problem.

I tried my best to clear my problem...i think its clear now??

can you help on it.

Thanks

|||

Hi,shehbazbashir is not online. Last active: 04-23-2007, 6:54 AM

According to your question, I think that you should write a .sql file which can restore and backup the database and run it in the query analyzer. So you can download or upload the backup file and attach it to the SqlServer.

Hope it helps.

|||Thanks It workedBig Smile

Friday, March 23, 2012

Importing related data

I need to recreate a SQL 2005 db in a SQL 2000 server. So far I was able to recreate database schema, but I don't know how to import the raw data. I tried copy-pasting table data to the new db using SQL Server Management Studio Express, but there's a problem - the identity columns do not retain the correct values. For example:

Old db table

ID Username
-
15 Peter
45 James

When pasting the same data to teh new db, I would get this:

ID Username

-

1 Peter

2 James

You can see the ID field values become different (since the ID attribute is an identity and the table in the new db is empty, the db picks the values 1 and 2 for the two records) and now when pasting data into related tables, I get the error because table relations (which are based on identities) do not match. How do I solve this problem and import all the data into the new db while maintaining correct relationships among tables?

Issue SET IDENTITY_INSERT <table-name> ON prior to loading the data! that enables you to load whatever values you like into there.

-Jamie

|||It probably works when running INSERT sentences, but I want to copy-paste data using SQL Server Management Studio Express.

Maybe there's a way to export the data into txt format by generate INSERTS?|||

Copying and pasting is not a method of moving data between tables.

Generating inserts is a very good idea. Do a search on the internet for sp_generate_inserts

-Jamie