element vs attribute!

I ran into an interesting issue.
Can anyone help explain why I'm able to load my xml data as an attribute but
I'm not able to load my data when it is configured as an element?
I have XML data that I bulkload into SQL2005. I'm able to load example data
into my second table by configuring my data as an attribute like this:
<SDOCL CLMSEQ="1" CLM="1. Reason for claim">
<SDOCL CLMSEQ="2" CLM="2. Reason for claim 2">
I modified my XSR mapping file (show below) to load the data as an Element
which would be a much better solution for me.
When I run my script I receive an error "Schema: missing element definition
'CLMSEQ'"
When I add the element type for CLMSEQ & CLM "<ElementType name="CLMSEQ"
dt:type="string" />" I receive this error "Datamapping to column CLM was
already found in the data. Make sure that no two schema definitions map to
the same column."
Any ideas?
My XML file is formatted like this:
<PATDOC>
<B110> 1 </B110>
<SDOCL>
<CLM> 1. Reason for this claim </CLM>
<CLMSEQ> 1 </CLMSEQ>
</SDOCL>
<SDOCL>
<CLM> 2. Reason for this claim </CLM>
<CLMSEQ> 2 </CLMSEQ>
</SDOCL>
<?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="B110" dt:type="string" />
<ElementType name="PATDOCS" sql:is-constant="1">
<element type="PATDOC" />
</ElementType>
<ElementType name="SDOCL" sql:relation="Claims" >
<element type="CLM" sql:field="CLM" />
<element type="CLMSEQ" sql:field="CLMSEQ" />
</ElementType>
<ElementType name="PATDOC" sql:relation="Pdata">
<element type="B110" sql:field="B110" />
<element type="SDOCL">
<sql:relationship
key-relation="Pdata"
key="B110"
foreign-key="B110"
foreign-relation="Claims" />
</element>
</ElementType>
</Schema>
This example xml and xsd work for me if I add the ElementType declarations.
However you might get the error message you referred to if you were to
include multiple CLM/CLMSEQ pairs under a SDOCL element like:
<SDOCL>
<CLM> 1. Reason for this claim </CLM>
<CLMSEQ> 1 </CLMSEQ>
<CLM> 2. Reason for this claim </CLM>
<CLMSEQ> 2 </CLMSEQ>
</SDOCL
If your xml was shaped like this you would need to add an additional layer
of elements to group the pairs together:
<SDOCL>
<CLMPAIR>
<CLM> 1. Reason for this claim </CLM>
<CLMSEQ> 1 </CLMSEQ>
</CLMPAIR>
<CLMPAIR>
<CLM> 2. Reason for this claim </CLM>
<CLMSEQ> 2 </CLMSEQ>
</CLMPAIR>
</SDOCL
"Charles W" wrote:

> I ran into an interesting issue.
>
> Can anyone help explain why I'm able to load my xml data as an attribute but
> I'm not able to load my data when it is configured as an element?
>
> I have XML data that I bulkload into SQL2005. I'm able to load example data
> into my second table by configuring my data as an attribute like this:
> <SDOCL CLMSEQ="1" CLM="1. Reason for claim">
> <SDOCL CLMSEQ="2" CLM="2. Reason for claim 2">
>
> I modified my XSR mapping file (show below) to load the data as an Element
> which would be a much better solution for me.
>
> When I run my script I receive an error "Schema: missing element definition
> 'CLMSEQ'"
> When I add the element type for CLMSEQ & CLM "<ElementType name="CLMSEQ"
> dt:type="string" />" I receive this error "Datamapping to column CLM was
> already found in the data. Make sure that no two schema definitions map to
> the same column."
>
> Any ideas?
>
>
> My XML file is formatted like this:
> <PATDOC>
> <B110> 1 </B110>
> <SDOCL>
> <CLM> 1. Reason for this claim </CLM>
> <CLMSEQ> 1 </CLMSEQ>
> </SDOCL>
> <SDOCL>
> <CLM> 2. Reason for this claim </CLM>
> <CLMSEQ> 2 </CLMSEQ>
> </SDOCL>
>
>
> <?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="B110" dt:type="string" />
>
> <ElementType name="PATDOCS" sql:is-constant="1">
> <element type="PATDOC" />
> </ElementType>
>
> <ElementType name="SDOCL" sql:relation="Claims" >
> <element type="CLM" sql:field="CLM" />
> <element type="CLMSEQ" sql:field="CLMSEQ" />
> </ElementType>
>
> <ElementType name="PATDOC" sql:relation="Pdata">
> <element type="B110" sql:field="B110" />
>
> <element type="SDOCL">
> <sql:relationship
> key-relation="Pdata"
> key="B110"
> foreign-key="B110"
> foreign-relation="Claims" />
> </element>
> </ElementType>
> </Schema>
>
>

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/