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

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 definitio
n
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
BodThere 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/d...r />
ml2k5.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/d.../>
rxml2k5.asp
>|||Bod-
Can you post your final schema file that you are using for this bulk
load?
Thanks
Brian
briankudera
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message2239821.html

Importing data & inserting value

I've got a bunch of CSV files (21) which all contain the same fields but are only distinguishable from their file names. I.e. file.001 file.002 etc. I need to collaborate them all into one table on our Datawarehouse. Im currently doing the following:

1. Drop & recreate a temp table with an additional column as a default value based on the corresponding file extension (manually entered into the DTS routine).
2. Import a single file into a temp table.
3. Transfer the temp table data to the permanent table with Keep Null Values ticked.

Ive only got as far as importing 2 of the files (which works!) but is getting messy already with 10 steps so far!

There must be an easy way as a file is being imported to add a value on each row rather then going through all these processes!

Can anyone help?

Thanks in advance!You can use the bcp import utility with a format file. See in BOL for more info on format files.