Hello.

I am a newbie to XML. I have been given a large xml file
that I need to import into sql server. I have access to
sql 7 and sql 2000.

This file is in a format I have never seen before, and do
not know where to begin.

Here is a sample of the file. I have removed the values
for the fields.

<?xml version="1.0" standalone="yes" ?>
<data created="2003-12-30 12:59:26.296">
<record model = "348" record_number = "0">
<field name = "PLU/SKU" type = "String" value = ""/>
<field name = "Product Description" type = "String" value
= ""/>
<field name = "Category" type = "String" value = ""/>
<field name = "Style" type = "String" value = ""/>
<field name = "Department" type = "String" value = ""/>
<field name = "Color" type = "String" value = ""/>
<field name = "Size1" type = "String" value = ""/>
<field name = "QTY On Hand" type = "Decimal" value = ""/>
<field name = "QTY Available" type = "Decimal" value = ""/>
<field name = "QTY Back Order" type = "Decimal" value
= ""/>
<field name = "Selling Price" type = "Decimal" value = ""/>
<field name = "Supplier" type = "String" value = ""/>
<record model = "359" record_number = "0">
<field name = "Location ID" type = "Reference to String"
value = ""/>
<field name = "Location Name" type = "String" value = ""/>
<field name = "PLU/SKU" type = "Reference to String" value
= ""/>
<field name = "Product Description" type = "String" value
= ""/>
<field name = "UPC" type = "String" value = ""/>
<field name = "Supplier" type = "String" value = ""/>
<field name = "Style" type = "String" value = ""/>
<field name = "Color" type = "String" value = ""/>
<field name = "Size1" type = "String" value = ""/>
<field name = "Size2" type = "String" value = ""/>
<field name = "Last Cost" type = "Decimal" value = ""/>
</record>
</record>
</data>

Can anyone point me in the right direction?

Thanks in advance.

DTS has no XML connector, would be nice though. If you send a mail to
sqlwish (AT) microsoft (DOT) com maybe you can give them your wish list.

As for importing XML. My first stop is generally www.sqlxml.org


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jennifer Smith" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Look for the SQLXMLBulkLoad Com Object. If you search
http://support.microsoft.com for this, you should find a sample article on
how to import XML data. I found it and used it this week myself. There is
a bug in it, but only that Microsoft put City where they meant State in the
sample data.

Let me know if you can't find the article or have questions about it.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

OK I tried the example for the SQLXMLBulkLoad Com Object and it works fine. I
guess my main stumbling block is that the data xml file I have is not in a
format I have seen before. I am trying to write a schema to match it, but am
getting errors.

Has anyone seen where the field names are specified as below, and not in the
usual
<sku>123</sku> format?


Kevin3NF wrote:

Hi Jennifer,

It seems that you are confused with the format of the data xml file
described in the following article:
316005 HOW TO: Import XML into SQL Server with the XML Bulk Load Component
http://support.microsoft.com/?id=316005
If I have misunderstood, please feel free to let me know.

Please refer to the following articles, which should help you a lot.
Articles on SQL Server Books Online:
Topic: "Creating XML Views Using Annotated XDR Schemas"

A Quick Guide to XML Schema
http://msdn.microsoft.com/msdnmag/is...04/xml/toc.asp
A Quick Guide to XML Schema-2
http://msdn.microsoft.com/msdnmag/is...lfiles/toc.asp

Thanks for posting in the community.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

May I take a stab at this. you mean the tags? (i.e. <sku>
value</sku>). If so then you should be able to see the
field names in the corresponding .XSD file if you're using
..NET. Correct me if I'm wrong people I'm new to this arena
myself.