How to insert Identity value to another table in SQLXML

[Maidenz08]

I am using SQLXML 4.0 to Bulkload data from the XML to the SQL Database. I have 2 tables. "Country" and "Customer". Below is my XML file format ("Data.xml")

<Root>
   <Countries>
       <Country Name="USA"></Country>
       <Country Name="Australia"></Country>
   </Countries>
   <Customers>
       <Customer Name="John Smith" CountryName="Australia"></Customer>
   </Customers>
</Root>
 

I have 2 tables

Country

Id Name

1 USA
2 Australia

Customer

Id CustomerName CountryId

1 John Smith 2

When i import my XML, "Id" column in the country is auto generated as it is identity(1,1). I want this "Id" value to sit in my Customer Table "CountryId" Column.

Note that I cannot nest the Customer Tag within the Country Tag. By nesting, i can easily define Parent and Child key in the XSD. But without nesting the elements i have not found a way to define these relationships.

Below is my XSD (Schema.xml)

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:annotation>
    <xsd:appinfo>
      <sql:relationship name="Country_Customer"
            parent="Country"
            parent-key="Id"
            child="Customer"
            child-key="CountryId" />
    </xsd:appinfo>
  </xsd:annotation>
  <xsd:element name="Root" sql:is-constant="1">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Countries" sql:is-constant="1">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element maxOccurs="unbounded" name="Country" sql:relation="Country">
                <xsd:complexType>
                  <xsd:attribute name="Name" type="xsd:string" use="required" />
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
        <xsd:element name="Customers" sql:is-constant="1">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="Customer" sql:relation="Customer" sql:relationship="Country_Customer">
                <xsd:complexType>
                  <xsd:attribute name="Name" type="xsd:string" use="required" sql:field="CustomerName" />
                  <xsd:attribute name="CountryName" type="xsd:string" use="required" sql:field="CountryId" />
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
 

I have defined an SQL relationship but i am specifying the Country Name in my XML and hence the relationship does not work.

Below are the table creation scripts

CREATE TABLE [dbo].[Country](
    [Id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [nvarchar](50) NULL,
 )
 
CREATE TABLE [dbo].[Customer](
    [Id] [bigint] IDENTITY(1,1) NOT NULL Primary Key,
    [CustomerName] [nvarchar](50) NULL,
    [CountryId] [bigint] NULL,
 )
 

I am using the below VB Script to bulk import

Dim FileValid
 
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=ServerName;database=databasename;User Id=username;Password=password"
objBL.ErrorLogFile = "c:\error.log"
objBL.KeepIdentity = False
 
'Validate the data file prior to bulkload
Dim sOutput 
sOutput = ValidateFile("Data.xml", "", "Schema.xml")
WScript.Echo sOutput
 
If FileValid Then
   ' Check constraints and initiate transaction (if needed)
   ' objBL.CheckConstraints = True
   ' objBL.Transaction=True
  'Execute XML bulkload using file.
  objBL.Execute "Schema.xml", "Data.xml"
  set objBL=Nothing
End If
 
Function ValidateFile(strXmlFile,strUrn,strXsdFile)
 
   ' Create a schema cache and add SampleSchema.xml to it.
   Dim xs, fso, sAppPath
   Set fso = CreateObject("Scripting.FileSystemObject") 
   Set xs = CreateObject("MSXML2.XMLSchemaCache.6.0")
   sAppPath = fso.GetFolder(".") 
   xs.Add strUrn, sAppPath & "\" & strXsdFile
 
   ' Create an XML DOMDocument object.
   Dim xd 
   Set xd = CreateObject("MSXML2.DOMDocument.6.0")
 
   ' Assign the schema cache to the DOM document.
   ' schemas collection.