How to Create an XSD/XML Schema file?

I am trying to load the following XML file into a database table. I
receive the file from an external source exactly as follows:
<list type='full' level='state' val='CA'>
<ac val='714'>
<ph val='2011882' />
<ph val='2013327' />
<ph val='2016543' />
<ph val='2017632' />
<ph val='2017656' />
<ph val='2018788' />
<ph val='2018211' />
<ph val='2019345' />
</ac>
</list>
I have a SQL Server 2000 running with a Table named PHONE that has only
one column:
PHONENUMBER <VARCHAR 10> (Sample Data: 7025556511)
I am new to XML as it works with SQL Server so I have the following
issues:
(1) Because the external file can have millions of PH records, I would
like to load the data into the SQL Server table using OPENXML and the
SQLXMLBulkLoad object (I understand this is a desirable option for
performance).
(2) How should the XSD Mapping Schema file be formatted to
combine/merge the AC and PH values into the PHONE table as one piece of
data?
(3) The bottom line is 'What will the XSD Mapping Schema file look
like'?
Any help would be greatly appreciated.> I am trying to load the following XML file into a database table. I
> receive the file from an external source exactly as follows:
> <list type='full' level='state' val='CA'>
> <ac val='714'>
> <ph val='2011882' />
> <ph val='2013327' />
> <ph val='2016543' />
> <ph val='2017632' />
> <ph val='2017656' />
> <ph val='2018788' />
> <ph val='2018211' />
> <ph val='2019345' />
> </ac>
> </list>
Since I am a developer, I am kind of weak on these XML-related features of
SQL Server. But since no one else has answered your post, I will tell you
what I would try as a developer.
In any of the .NET programming languages, you have this thing called a
DataSet object, which represents a series of DataTable objects. A DataTable
represents a resultset, like you would get from a SELECT statement. A
DataSet can contain any number of DataTables, and can even enforce
referential integrity between them. A DataSet object has a OpenXML or
ReadXML method (I forget which -- I haven't slept in days) where you can
pass the filename of your XML file, and your DataSet will be populated with
the relational structure and data that's represented inside the XML file.
Then you enumerate all the DataTable objects inside the DataSet.Tables
collection. For each DataTable, you build a InsertCommand or UpdateCommand
for a DataAdapter (the thing that issues commands to the data store)
Then you say DataSet.Update() and presto, all your data is in the database.
In theory.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||<dsijta@.netscape.net> wrote in message
news:1140837455.552719.197710@.v46g2000cwv.googlegroups.com...
>I am trying to load the following XML file into a database table. I
> receive the file from an external source exactly as follows:
> <list type='full' level='state' val='CA'>
> <ac val='714'>
> <ph val='2011882' />
> <ph val='2013327' />
> <ph val='2016543' />
> <ph val='2017632' />
> <ph val='2017656' />
> <ph val='2018788' />
> <ph val='2018211' />
> <ph val='2019345' />
> </ac>
> </list>
> I have a SQL Server 2000 running with a Table named PHONE that has only
> one column:
> PHONENUMBER <VARCHAR 10> (Sample Data: 7025556511)
> I am new to XML as it works with SQL Server so I have the following
> issues:
> (1) Because the external file can have millions of PH records, I would
> like to load the data into the SQL Server table using OPENXML and the
> SQLXMLBulkLoad object (I understand this is a desirable option for
> performance).
> (2) How should the XSD Mapping Schema file be formatted to
> combine/merge the AC and PH values into the PHONE table as one piece of
> data?
> (3) The bottom line is 'What will the XSD Mapping Schema file look
> like'?
> Any help would be greatly appreciated.
>
Here's an example of an XSD for the document you posted. Many XML utilities
will generate schemas automatically for you. This one I produced using SQL
Server 2005 Integration Services.
An XSD only validates the format. It doesn't perform any transformations.
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="list">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ac">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="ph">
<xs:complexType>
<xs:attribute name="val" type="xs:unsignedInt"
use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="val" type="xs:unsignedShort" use="optional"
/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="type" type="xs:string" use="optional" />
<xs:attribute name="level" type="xs:string" use="optional" />
<xs:attribute name="val" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:schema>
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can also this free tool (from MS):
http://apps.gotdotnet.com/xmltools/...e/overview.html
http://www.gotdotnet.com/Community/...0c-f24b91e47228
-oj
<dsijta@.netscape.net> wrote in message
news:1140837455.552719.197710@.v46g2000cwv.googlegroups.com...
>I am trying to load the following XML file into a database table. I
> receive the file from an external source exactly as follows:
> <list type='full' level='state' val='CA'>
> <ac val='714'>
> <ph val='2011882' />
> <ph val='2013327' />
> <ph val='2016543' />
> <ph val='2017632' />
> <ph val='2017656' />
> <ph val='2018788' />
> <ph val='2018211' />
> <ph val='2019345' />
> </ac>
> </list>
> I have a SQL Server 2000 running with a Table named PHONE that has only
> one column:
> PHONENUMBER <VARCHAR 10> (Sample Data: 7025556511)
> I am new to XML as it works with SQL Server so I have the following
> issues:
> (1) Because the external file can have millions of PH records, I would
> like to load the data into the SQL Server table using OPENXML and the
> SQLXMLBulkLoad object (I understand this is a desirable option for
> performance).
> (2) How should the XSD Mapping Schema file be formatted to
> combine/merge the AC and PH values into the PHONE table as one piece of
> data?
> (3) The bottom line is 'What will the XSD Mapping Schema file look
> like'?
> Any help would be greatly appreciated.
>|||> http://apps.gotdotnet.com/xmltools/...e/overview.html
WOAH! COOL!!
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||Hi -
For an example of a XML schema generator utility, see:
http://www.stylusstudio.com/autogen_xsd.html
It shows how to create an XML schema from an XML instance document.
hope that helps.
Sincerely,
The Stylus Studio Team
http://www.stylusstudio.comsql