Friday, March 23, 2012

Importing Multiple XML Field values

Hi,
Does anyone know how to make SQLXMLBulkLoad accept a SQL file if it has a
field repeated with more than 1 value. For example
<ROOT>
<Cust>
<details>
<Val1>23</Val1>
<Val2>44</Val2>
<Val3>16</Val3>
<Val3>77</Val3>
<Val4>47</Val4>
</details>
</Cust>
</ROOT>
As Val3 appears twice in the file, SQLXMLBulkLoad fails because it already
has a mapping for this field. Is there a way to modify the schema definition
to allow for this?
I don't actually care which of the values for Val3 the import process saves,
although I would like one or the other.
The schema currently looks something like:
<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<element name="Cust" sql:relation="CustTable" >
<complexType>
<sequence>
<element name="Val1" sql:field="CusVal1" type="integer" />
<element name="Val2" sql:field="CusVal2" type="integer" />
<element name="Val3" sql:field="CusVal3" type="integer" />
<element name="Val4" sql:field="CusVal4" type="integer" />
<sequence>
<complexType>
<element>
<schema>
Thanks
Bod
There is no way to get bulkload to work this way.
Your options would be to:
1. clean the data ahead of time using XSLT, or
2. do the inserts using the Server's nodes() method
This article has examples of using nodes()
http://msdn.microsoft.com/library/de.../forxml2k5.asp
|||Thanks Todd. Not my favourite answer but your confirmation that I can't do
it saves me wasting hours of trying...
Bod
"Todd Pfleiger [MSFT]" wrote:

> There is no way to get bulkload to work this way.
> Your options would be to:
> 1. clean the data ahead of time using XSLT, or
> 2. do the inserts using the Server's nodes() method
> This article has examples of using nodes()
> http://msdn.microsoft.com/library/de.../forxml2k5.asp
>
sql

No comments:

Post a Comment