Bulk importing XML to SQL 2000

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: Bulk importing XML to SQL 2000
    ... The data you are trying to bulkload doesn't match the schema definition, ... XML file to the text field in a table but cannot pass the text data to the ... we found the XML format is different from the one in the web ... This format can be used by OPENXML but cannot use the Bulk Importing. ...
    (microsoft.public.sqlserver.xml)
  • Re: XML Buffer, OPENXML Usage Advice
    ... The memory available for OpenXML is 1/8th of available server memory (I ... >attempting to replace some looping processes with batch processes. ... but so far the XML passed into the proc has been less than 2KB ...
    (microsoft.public.sqlserver.xml)
  • can openxml write multiple fields - 1 row?
    ... quantity attributes from the XML document. ... declare @doc varchar ... FROM OPENXML ... This routine only generates one int ...
    (microsoft.public.sqlserver.xml)
  • Re: openxml seemingly fails to process external entity
    ... > External entities are not supported with OpenXML for a variety of security ... > Best is to resolve them on the client/midtier side before sending the XML to ... >> Here are the beginnings of the lines I see in SQL Query Analyzer: ... >> You can verify that this external entity stuff is done properly by opening ...
    (microsoft.public.sqlserver.xml)
  • Re: openxml question
    ... Avoid OPENXML over large XML documents. ... Avoid large numbers of concurrent OPENXML statements over XML ...
    (microsoft.public.sqlserver.xml)