SQLXMLBulkLoad: Import nested XML fails

Question

SQLXMLBulkLoad: Import nested XML fails

matt-988012

SSC Journeyman

Points: 87
More actions
February 12, 2010 at 4:08 am

#98539

Hi,
I've got a XML file like this (via a third-party-vendor, can't change it) and want to import it into my MSSQL2k database via SQLXMLBulkLoad:
<References>
<Reference>
<Id>1</Id>
<Name>Test</Name>
<ReferenceDatas>
<ReferenceData>
<Data1>Test1</Data1>
</ReferenceData>
</ReferenceDatas>
</Reference>
</References>
There is always only one ReferenceData, despite being in ReferenceDatas.
My mapping file looks like this:
<?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="Id" dt:type="int" />
<ElementType name="Name" dt:type="string" />
<ElementType name="Data1" dt:type="string" />
<ElementType name="References" sql:is-constant="1">
<element type="Reference" />
</ElementType>
<ElementType name="Reference" sql:relation="xmlTest">
<element type="Id" sql:field="id" />
<element type="Name" sql:field="name" />
<element type="Data1" sql:field="value" />
</ElementType>
</Schema>
I want to import everything into this table:
CREATE TABLE [dbo].[xmlTest] (
[id] [int] NULL ,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[value] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
The "value" column (XML: Data1) always carries NULL.
Can you help me please how the correct mapping file has to look? I read already about "sql:relation", but I think thats only if you want to fill your data into more tables? It didn't worked when I tried to use it with the same table...
Thanks in advance! 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

As far as I can see the problem is that your Data1 element is part of ReferenceDatas/ReferenceData not but you declare it as being part of the Reference node.

Try to change

<ElementType name="Reference" sql:relation="xmlTest">

<element type="Id" sql:field="id" />

<element type="Name" sql:field="name" />

<element type="Data1" sql:field="value" />

</ElementType>

to a separate declaration

<ElementType name="Reference" sql:is-constant="1">

<element type="ReferenceDatas" />

</ElementType>

<ElementType name="ReferenceDatas" sql:is-constant="1">

<element type="ReferenceData" />

</ElementType>

<ElementType name="ReferenceData" sql:relation="xmlTest">

<element type="Data1" sql:field="value" />

</ElementType>

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

matt-988012 SSC Journeyman Points: 87 More actions · Answer 2

Hi lmu92,

thanks for your answer!

This way I get the "value" column filled, but the id and name column stays empty...(of course, there is no sql:field property for them)

If I replace

<ElementType name="Reference" sql:is-constant="1">

<element type="ReferenceDatas" />

</ElementType>

with

<ElementType name="Reference" sql:is-constant="1">

<element type="id" sql:field="id" />

<element type="name" sql:field="name" />

<element type="ReferenceDatas" />

</ElementType>

I get a 0x8000FFFF error - ~default relation can not be made~ (roughly translated...)

But I don't know what i should make it relate to...?