Re: Bulk importing XML to SQL 2000

I know the problem. The problem is in the schema.

Ivan

"Ivan" <ivan@xxxxxxxxxxxxx> ¼¶¼g©ó¶l¥ó·s»D:ejty019LHHA.140@xxxxxxxxxxxxxxxxxxxxxxx
We are trying to do the Bulk importing XML to SQL 2000 and facing some
problem.

At first, we want to use OPENXML. We can use the bulk insert to import the
XML file to the text field in a table but cannot pass the text data to the
local variable as local variable does not support text and the XML is sure
more than 8000 characters.

So, we try to follow the instruction in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp

However, we found the XML format is different from the one in the web
site. It is similar as below:

<TestingData>
<Details Field1="A" Field2="B" Field3="C" Field4="D" Field5="E"
Field6="F" Field7="G" />
</TestingData>

This format can be used by OPENXML but cannot use the Bulk Importing.

I have set the schema as follows:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Details" sql:relation="ImportTest" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Field1" type="xsd:string" />
<xsd:element name="Field2" type="xsd:string" />
<xsd:element name="Field3" type="xsd:string" />
<xsd:element name="Field4" type="xsd:string" />
<xsd:element name="Field5" type="xsd:string" />
<xsd:element name="Field6" type="xsd:string" />
<xsd:element name="Field7" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

After running the VBScript, we found the number of records is correct but
all fields become null.

Is there other method we can do?

Ivan



.

Relevant Pages

  • Re: OPENXML doesnt work with inline XDR Schema
    ... I guess I was looking for the answer in OPENXML ... "Graeme Malcolm" wrote in message ... > I have a stored procedure that uses OPENXML and accepts and entire XML ... > created with a default inline XDR schema by adding the XMLDATA clause, ...
    (microsoft.public.sqlserver.xml)
  • Re: How to decode binary base 64 data using OPENXML
    ... The first article you reference there mentions using a schema to specify the ... data type as bin.base64 and then use an updategram or bulk loading. ... seems that there is no way to use a schema with OPENXML? ...
    (microsoft.public.sqlserver.xml)
  • Capturing Invalid Records for XML Inserts
    ... I'm new to Xml inserts into sql server. ... I've used "OpenXML" ... to conduct an insert for single rows. ... capture invalid records from the bulk insert, ...
    (microsoft.public.sqlserver.xml)
  • Re: What is fastest way to insert many records into a SQL server database?
    ... To use OPENXML do I have to output an XML file to be imported by SQL Server ... (in much the same way as the BULK INSERT method) ...
    (microsoft.public.dotnet.languages.csharp)