Re: Import XML from Command-Line into SQL

From: Graeme Malcolm (graemem_cm_at_hotmail.com)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 10:12:10 +0100

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;uid
=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.xs
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?
>
>
>.
>

Relevant Pages

  • Re: WSH in VS.NET 2003?
    ... or will wsh xml include a ... Get the "TechNet Script Center Sample Scripts" ... Other|Useful Scripting Technologies|Saving Data in XML Format ... There is no schema for getting MS. ...
    (microsoft.public.scripting.wsh)
  • Re: Import XML from Command-Line into SQL
    ... the XP Test PC I am running the vbs from. ... Set objBL = CreateObject ... >I'd probably create a mapping schema and a VB Script ... that uses the SQL XML ...
    (microsoft.public.sqlserver.xml)
  • Re: SQLXML BulkLoad Question
    ... the main issue seems to be that the schema defines the fields as ... but they're attributes in the actual XML. ... Set objBL = CreateObject ... "Graeme Malcolm" wrote: ...
    (microsoft.public.sqlserver.xml)
  • Re: SQLXML BulkLoad Question
    ... I have used or tried to use xml spy to write this schema, and to be honest, ... Set objBL = CreateObject ... > I have a XML file and have written a schema to import the XML data into ...
    (microsoft.public.sqlserver.xml)
  • Re: Designing an XML Input Doc for Application
    ... internal script, but can it be done with a DTD ... You need a validating parser and use that to validate data.xml against rules.xml (assuming rules.xml is a schema in a schema language the parser supports). ... For W3C XML schemas an instance document can indicate schemas with the xsi:schemaLocation attribute. ... If you do the validation from the command line or with an XML editor then you don't need script respectively your own program to perform the validation. ...
    (comp.text.xml)