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/d...>
oad_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?
IvanI know the problem. The problem is in the schema.
Ivan
"Ivan" <ivan@.microsoft.com> glsD:ejty019LHHA.140@.TK2MSFTNGP04.phx.gbl...rkred">
> 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/d...
kload_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
>|||Hello,
The data you are trying to bulkload doesn't match the schema definition,
that's why you are getting NULLs.
The schema describes Field1, 2, 3... as elements but they are attributes in
the data file.So you have to change either the schema or the data to make it
work.
Hope this helps.
Regards,
Monica Frintu
"Ivan" wrote:

> I know the problem. The problem is in the schema.
> Ivan
> "Ivan" <ivan@.microsoft.com> ???g?ó?l￥ó·s?D:ejty019LHHA.140@.TK2MS
FTNGP04.phx.gbl...
>
>