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>
>
>