Re: Import XML from Command-Line into SQL
anonymous_at_discussions.microsoft.com
Date: 08/24/04
- Next message: Sher: "Re: can't use identity column in where clause."
- Previous message: noli: "Using bcp to export XML Files - strange characters"
- In reply to: Graeme Malcolm: "Re: Import XML from Command-Line into SQL"
- Next in thread: Sean: "Re: Import XML from Command-Line into SQL"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 24 Aug 2004 04:42:27 -0700
Graeme,
Again thanks for the response. I found the problem. The
table I was inserting into had a Identity/Primary Key. I
had to add objBL.KeepIdentity=False and define the field
in the schema to the .vbs file.
Thanks for all your help.
Sean
>-----Original Message-----
>Your data includes a CategoryID attribute in the
AQL_ERaterImports element,
>but it's not defined in the schema. Can you post the
table definition? It
>would help troubleshoot this.
>
>Cheers,
>Graeme
>
>--
>----
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com
>
>
><anonymous@discussions.microsoft.com> wrote in message
>news:b7a801c4891c$a64b5160$a401280a@phx.gbl...
>Thanks for the quick reply. Here is the error I get on
>the XP Test PC I am running the vbs from. This XP PC has
>the SQLXML 3.0 SP2 installed on it. I do belive the vbs
>is connecting to the db because it errored previous to a
>bad column mapping error.
>
>Error from Command-Line:
>
>ERaterImport.vbs(5, 1) Microsoft OLE DB Provider for SQL
>Server: The statement has been terminated.
>
>The data xml:
>
><?xml version="1.0" ?>
><ROOT>
> <AQL_ERaterImports CategoryID="1">
> <QuoteNumber>e-1004804977</QuoteNumber>
> <FirstSavedDate>12/29/2003 12:08:54
PM</FirstSavedDate>
> <LastSavedDate>12/29/2003 1:07:40 PM</LastSavedDate>
> <AgentNumber>1234567</AgentNumber>
> <InsuredName>p-wc min2</InsuredName>
> <SICcode>0781</SICcode>
> <CU></CU>
> <CBT>C</CBT>
> <CA></CA>
> <WC>WC</WC>
> <Premium>996</Premium>
>
> </AQL_ERaterImports>
></ROOT>
>
>
>
>Here is the ERaterImportsMapping.XSD:
>
><?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="QuoteNumber" dt:type="int" />
> <ElementType name="FirstSavedDate" dt:type="int" />
> <ElementType name="LastSavedDate" dt:type="int" />
> <ElementType name="AgentNumber" dt:type="int" />
> <ElementType name="InsuredName" dt:type="int" />
> <ElementType name="SICcode" dt:type="int" />
> <ElementType name="CU" dt:type="int" />
> <ElementType name="CBT" dt:type="int" />
> <ElementType name="CA" dt:type="int" />
> <ElementType name="WC" dt:type="int" />
> <ElementType name="Premium" dt:type="int" />
>
> <ElementType name="ROOT" sql:is-constant="1">
> <element type="AQL_ERaterImports" />
> </ElementType>
>
> <ElementType name="AQL_ERaterImports"
>sql:relation="AQL_ERaterImports">
> <element type="QuoteNumber"
>sql:field="QuoteNumber" />
> <element type="FirstSavedDate"
>sql:field="FirstSavedDate" />
> <element type="LastSavedDate"
>sql:field="LastSavedDate" />
> <element type="AgentNumber"
>sql:field="AgentNumber" />
> <element type="InsuredName"
>sql:field="InsuredName" />
> <element type="SICcode"
>sql:field="SICCode" />
> <element type="CU" sql:field="CU" />
> <element type="CBT" sql:field="CBT" />
> <element type="CA" sql:field="CA" />
> <element type="WC" sql:field="WC" />
> <element type="Premium"
>sql:field="Premium" />
> </ElementType>
>
></Schema>
>
>The ERaterImports.vbs: (I removed content and replaced
>wiht **** for security reasons)
>
>Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
>objBL.ConnectionString
>= "provider=SQLOLEDB.1;Server=*********;database=AQLDB;ui
d
>=sa;pwd=**********"
>'objBL.ErrorLogFile
>= "Q:\USERS\FTP\Test\PCTeam\LOG\ERaterImportError.log"
>objBL.ErrorLogFile
>= "Q:\USERS\FTP\Test\XX\ERaterImportError.log"
>objBL.Execute "Q:\USERS\FTP\Test\XX\ERaterImportMapping.x
s
>d", "Q:\USERS\FTP\Test\XX\ERaterImportData.xml"
>Set objBL = Nothing
>
>
>>-----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: "Re: can't use identity column in where clause."
- Previous message: noli: "Using bcp to export XML Files - strange characters"
- In reply to: Graeme Malcolm: "Re: Import XML from Command-Line into SQL"
- Next in thread: Sean: "Re: Import XML from Command-Line into SQL"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
