Re: Import XML from Command-Line into SQL
From: Sean (sean_cottrell_at_hotmail.com)
Date: 08/23/04
- Next message: Sher: "can't use identity column in where clause."
- Previous message: anonymous_at_discussions.microsoft.com: "Re: Import XML from Command-Line into SQL"
- In reply to: Graeme Malcolm: "Re: Import XML from Command-Line into SQL"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 23 Aug 2004 07:29:33 -0700
Graeme,
Sorry forgot to post as myself. Please see my first
reply listed as anonymous.
Thanks,
Sean
>-----Original Message-----
>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: Sher: "can't use identity column in where clause."
- Previous message: anonymous_at_discussions.microsoft.com: "Re: Import XML from Command-Line into SQL"
- In reply to: Graeme Malcolm: "Re: Import XML from Command-Line into SQL"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
