Element positioning in bulk load XSD

Hello,
I have a question about how I should structure my schema so that I
can insert an element from another node with elements from a separate
node.
The following is an example of the xml file:
<?xml version="1.0" encoding="utf-8" ?>
<interventions>
<info>
<title>Title of this particular extract</title>
<description>this is the description of this particular
extract file</description>
<vendorName>Vendor xxx</vendorName>
</info>
<!-- start one, specific intervention -->
<intervention refid='AAF5C40D-9147-46F9-973C-62981497BD43'
versionDate='01/02/2004' version='2.02.001'>
<title>the title of this intervention</title>
<description>the description of this
intervention</description>
<classification>SOME GEM STRING</classification>
<grades>
<!-- grade has legal value (so far) of k through 12 -->
<grade>k</grade>
<grade>2</grade>
</grades>
<externalLinks>
<!-- links to supporting documentation -->
<url>http://some/link/to/some/supporting/doc</url>
<url>http://another/link/to/some/supporting/doc</url>
<url>http://yet/another/link/to/some/supporting/doc</url>
</externalLinks>
</intervention>
</interventions>
So, what I want to figure out is how the element vendorName in the
Info grouping into the same row as the information in the Intervention
Node. So that I can put them into the same row in the target table.
FYI there are three tables involved in this, but the table I want to
insert into is ImportQueueIntervention2. Everything is working except
I do not understand how to get the information in the VENDORNAME
element into the same row (yes it is a repeating piece of information
for every row in the file).
Okay here is the schama:
<?xml version="1.0" ?>
<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="InterventionToLink"
parent="ImportQueueIntervention2"
parent-key="ImportQueueInterventionID"
child="ImportQueueInterventionLink"
child-key="ImportQueueInterventionID" />
<sql:relationship name="InterventionToGrade"
parent="ImportQueueIntervention2"
parent-key="ImportQueueInterventionID"
child="ImportQueueInterventionGrade"
child-key="ImportQueueInterventionID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="intervention"
sql:relation="ImportQueueIntervention2">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="title" sql:field="ExtractTitle" type="xsd:string"
sql:datatype="varchar(300)" />
<xsd:element name="description" sql:field="ExtractDescription"
type="xsd:string" sql:datatype="varchar(1000)" />
<xsd:element name="classification" sql:field="ExtractContentAreas"
type="xsd:string" sql:datatype="varchar(500)" />
<xsd:element name="grades" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="grade"
sql:relation="ImportQueueInterventionGrade"
sql:relationship="InterventionToGrade" sql:field="ExtractGrade"
type="xsd:string" sql:datatype="varchar(25)" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="externalLinks" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="url" sql:relation="ImportQueueInterventionLink"
sql:relationship="InterventionToLink" sql:field="ExtractLink"
type="xsd:string" sql:datatype="varchar(1000)"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="refid" sql:field="ExtractGUID" type="xsd:string"
sql:datatype="uniqueidentifier" />
<xsd:attribute name="versionDate" sql:field="CreationDate"
type="xsd:dateTime" sql:datatype="dateTime" />
<xsd:attribute name="version" sql:field="ExtractVersion"
type="xsd:string" sql:datatype="varchar(10)" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
Any assistance would be greatly appreicated.
Thank you,
Jim
Unfortunately, that is not possible with current SqlXml bulkload / mapping
schema support. But there are a couple of other options:
1. Use OpenXml via the column map in the WITH clause.
2. Preprocess the Xml with an XSLT which makes the vendorName element an
child of the intervention element.
3. Load the data into temp tables and merge on the server.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/