Forum Moderators: open
----------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
- <root>
- <item>
<field1>AAAA</AirportCityCode>
<field2>BBBB</PropertyID>
<field3>CCCC</HotelName>
- <Categery1>
<field4>DDDD</Street1>
<field5>EEEE</Street2>
</Categery1>
- <Categery2>
<field6>FFFF</Street1>
<field7>GGGG</Street2>
</Categery2>
</item>
- <item>
<field1>AAAA</AirportCityCode>
<field2>BBBB</PropertyID>
<field3>CCCC</HotelName>
- <Categery1>
<field4>DDDD</Street1>
<field5>EEEE</Street2>
</Categery1>
- <Categery2>
<field6>FFFF</Street1>
<field7>GGGG</Street2>
</Categery2>
</item>
</root>
----------------------------------------
Our SQL schema is setup 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="Field1" dt:type="text" />
<ElementType name="Field2" dt:type="text" />
<ElementType name="Field3" dt:type="text" />
<ElementType name="Field4" dt:type="text" />
<ElementType name="Field5" dt:type="text" />
<ElementType name="Field6" dt:type="text" />
<ElementType name="Field7" dt:type="text" />
<ElementType name="Root" sql:is-constant="1">
<element type="Item" />
</ElementType>
<ElementType name="Item" sql:relation="MyTable">
<element type="Field1" sql:field="DBField1" />
<element type="Field2" sql:field="DBField2" />
<element type="Field3" sql:field="DBField3" />
<element type="Field4" sql:field="DBField4" />
<element type="Field5" sql:field="DBField5" />
<element type="Field6" sql:field="DBField6" />
<element type="Field7" sql:field="DBField7" />
</ElementType>
</Schema>
----------------------------------------
…and we use the following code in VB.NET to use XML bulk load…
----------------------------------------
Dim objbl As Object
objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objbl.ConnectionString = "connectionstring"
objBL.ErrorLogFile = "c:\error.log"
objbl.Execute("[schemafilepath]", "[xmlfilepath]")
objBL = Nothing
----------------------------------------
It is loading the data into the tables but it is only uploading fields 1, 2, and 3. None of the nested fields are getting inserted into the database and I would assume it’s because they are nested. That’s the only thing those fields have in common.
What do we need to do to our schema file to make sure that the fields 4,5,6, and 7 in the above example make it into their respective database fields?
