Re: Import XML from Command-Line into SQL
From: Graeme Malcolm (graemem_cm_at_hotmail.com)
Date: 08/23/04
- Next message: Peter Durica: "parse XML larger than 8000 characters"
- Previous message: Sean: "Import XML from Command-Line into SQL"
- In reply to: Sean: "Import XML from Command-Line into SQL"
- Next in thread: anonymous_at_discussions.microsoft.com: "Re: Import XML from Command-Line into SQL"
- Reply: anonymous_at_discussions.microsoft.com: "Re: Import XML from Command-Line into SQL"
- Reply: Sean: "Re: Import XML from Command-Line into SQL"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 23 Aug 2004 08:59:50 +0100
I'd probably create a mapping schema and a VB Script that uses the SQL XML
Bulk Load component to bulk load the data. Download SQLXML
(http://www.microsoft.com/downloads/details.aspx?familyid=4c8033a9-cf10-4e22-8004-477098a407ac&displaylang=en)
and have a look at the documentation. Here's a simple example to get you
started (It loads data into the Categories and Products tables in the
Northwind database):
XML file (Catalog.xml):
<?xml version="1.0" ?>
<Catalog>
<Category CategoryID="99">
<CategoryName>Scottish Foods</CategoryName>
<Description>Traditional food from Scotland</Description>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Category>
<Category CategoryID="100">
<CategoryName>Scottish Drinks</CategoryName>
<Description>Traditional drinks from Scotland</Description>
<Product ProductID="103">
<ProductName>Single Malt Whisky</ProductName>
<UnitPrice>100</UnitPrice>
</Product>
</Category>
</Catalog>
Schema file (Catalog.xsd):
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Catalog" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Category" sql:relation="Categories">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CategoryName" type="xsd:string"
sql:field="CategoryName"
sql:datatype="nvarchar(15)" />
<xsd:element name="Description" type="xsd:string"
sql:field="Description" sql:datatype="ntext" />
<xsd:element name="Product" sql:relation="Products">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="Categories"
parent-key="CategoryID"
child="Products"
child-key="CategoryID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductName" type="xsd:string"
sql:field="ProductName"
sql:datatype="nvarchar(40)" />
<xsd:element name="UnitPrice" type="xsd:decimal"
sql:field="UnitPrice" sql:datatype="money"
/>
</xsd:sequence>
<xsd:attribute name="ProductID" type="xsd:integer"
sql:field="ProductID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="CategoryID" type="xsd:integer"
sql:field="CategoryID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
VB Script:
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = _
"provider=SQLOLEDB;Server=(local);database=Northwind;" & _
"Integrated Security=SSPI;"
objBulkLoad.Execute "Catalog.xsd", "Catalog.xml"
Set objBulkLoad = Nothing
MsgBox "Catalog Imported"
Hope that helps,
Graeme
-- ---- Graeme Malcolm Principal Technologist Content Master Ltd. www.contentmaster.com "Sean" <sean_cottrell@hotmail.com> wrote in message news:aadd01c4888c$71c28930$a501280a@phx.gbl... HI, I am try to accomplish the following: Every day I will get a random number of XML files with a random number of row entries. each of these files will have a different file name but be in a specific file name format such as file1.xml,file2.xml, etc. These files will be "dumped" to a network drive at random times. We already have a program written that will monitor this directory and when I file gets "dumped" into it, will fire of a script. I am looking for the best way to take each of these .xml files and import them into a MS SQL Table. The files are very simple containing only 9 columns per row of data. Also, we would prefer that this script execute from a remote server other than the server that is running MS SQL. Can anybody suggest the best way to do this?
- Next message: Peter Durica: "parse XML larger than 8000 characters"
- Previous message: Sean: "Import XML from Command-Line into SQL"
- In reply to: Sean: "Import XML from Command-Line into SQL"
- Next in thread: anonymous_at_discussions.microsoft.com: "Re: Import XML from Command-Line into SQL"
- Reply: anonymous_at_discussions.microsoft.com: "Re: Import XML from Command-Line into SQL"
- Reply: Sean: "Re: Import XML from Command-Line into SQL"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
