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>
>
>
Showing posts with label element. Show all posts
Showing posts with label element. Show all posts
Friday, February 24, 2012
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/
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/
Element in datagrid?
please help me to take element in datagrid?Hi, welcome to the ASP.NET Forums!
Could you please give us more information? Also, this forum is for SQL Server and MSDE. If your question concerns datagrids you should probably post it here:DataGrid, DataList, Repeater Controls
Terri
element centric XML for XML explicit - suggestion needed
my xml would be like.
<X>
-<XY>
-<XYZ>
<![CDATA[ hello adkfsd ]]>
</XYZ>
-<XYZ1>
<![CDATA[ hell adkfsd ]]>
</XYZ1>
<XY>
<XY1>
...
</XY1
</X>
it is very element centric . All the data comes from a single table.
here just for mentioning the parent tag for the datas i need to write a
select.
i wrote a query which had 6 selects and got the XML.
two of the select querys were written just to include the name for the
parent tag.
can someone pls tell me if its possible to improvise..
is it possible to use for xml auto and get CDATA..
waiting for ur replies,
tobyToby wrote:
> my xml would be like.
> <X>
> -<XY>
> -<XYZ>
> <![CDATA[ hello adkfsd ]]>
> </XYZ>
> -<XYZ1>
> <![CDATA[ hell adkfsd ]]>
> </XYZ1>
> <XY>
> <XY1>
> ...
> </XY1
> </X>
>
> it is very element centric . All the data comes from a single table.
> here just for mentioning the parent tag for the datas i need to write a
> select.
> i wrote a query which had 6 selects and got the XML.
> two of the select querys were written just to include the name for the
> parent tag.
> can someone pls tell me if its possible to improvise..
> is it possible to use for xml auto and get CDATA..
Do you really need CDATA Sections? They are often unnecessary.
See http://xml.silmaril.ie/authors/cdata/
///Peter|||I have to second Peter's sentiment about CDATA sections. CDATA sections are
meant for helping manual authoring of XML documents and should not be used
otherwise.
And no, FOR XML AUTO does not provide a way to generate CDATA sections. You
would have to use the EXPLICIT mode.
Best regards
Michael
"Peter Flynn" <peter.nosp@.m.silmaril.ie> wrote in message
news:3sahonFn6kgqU4@.individual.net...
> Toby wrote:
>
> Do you really need CDATA Sections? They are often unnecessary.
> See http://xml.silmaril.ie/authors/cdata/
> ///Peter
>
<X>
-<XY>
-<XYZ>
<![CDATA[ hello adkfsd ]]>
</XYZ>
-<XYZ1>
<![CDATA[ hell adkfsd ]]>
</XYZ1>
<XY>
<XY1>
...
</XY1
</X>
it is very element centric . All the data comes from a single table.
here just for mentioning the parent tag for the datas i need to write a
select.
i wrote a query which had 6 selects and got the XML.
two of the select querys were written just to include the name for the
parent tag.
can someone pls tell me if its possible to improvise..
is it possible to use for xml auto and get CDATA..
waiting for ur replies,
tobyToby wrote:
> my xml would be like.
> <X>
> -<XY>
> -<XYZ>
> <![CDATA[ hello adkfsd ]]>
> </XYZ>
> -<XYZ1>
> <![CDATA[ hell adkfsd ]]>
> </XYZ1>
> <XY>
> <XY1>
> ...
> </XY1
> </X>
>
> it is very element centric . All the data comes from a single table.
> here just for mentioning the parent tag for the datas i need to write a
> select.
> i wrote a query which had 6 selects and got the XML.
> two of the select querys were written just to include the name for the
> parent tag.
> can someone pls tell me if its possible to improvise..
> is it possible to use for xml auto and get CDATA..
Do you really need CDATA Sections? They are often unnecessary.
See http://xml.silmaril.ie/authors/cdata/
///Peter|||I have to second Peter's sentiment about CDATA sections. CDATA sections are
meant for helping manual authoring of XML documents and should not be used
otherwise.
And no, FOR XML AUTO does not provide a way to generate CDATA sections. You
would have to use the EXPLICIT mode.
Best regards
Michael
"Peter Flynn" <peter.nosp@.m.silmaril.ie> wrote in message
news:3sahonFn6kgqU4@.individual.net...
> Toby wrote:
>
> Do you really need CDATA Sections? They are often unnecessary.
> See http://xml.silmaril.ie/authors/cdata/
> ///Peter
>
element centric XML for XML explicit - suggestion needed
my xml would be like.
<X>
-<XY>
-<XYZ>
<![CDATA[ hello adkfsd ]]>
</XYZ>
-<XYZ1>
<![CDATA[ hell adkfsd ]]>
</XYZ1>
<XY>
<XY1>
...
</XY1
</X>
it is very element centric . All the data comes from a single table.
here just for mentioning the parent tag for the datas i need to write a
select.
i wrote a query which had 6 selects and got the XML.
two of the select querys were written just to include the name for the
parent tag.
can someone pls tell me if its possible to improvise..
is it possible to use for xml auto and get CDATA..
waiting for ur replies,
toby
Toby wrote:
> my xml would be like.
> <X>
> -<XY>
> -<XYZ>
> <![CDATA[ hello adkfsd ]]>
> </XYZ>
> -<XYZ1>
> <![CDATA[ hell adkfsd ]]>
> </XYZ1>
> <XY>
> <XY1>
> ...
> </XY1
> </X>
>
> it is very element centric . All the data comes from a single table.
> here just for mentioning the parent tag for the datas i need to write a
> select.
> i wrote a query which had 6 selects and got the XML.
> two of the select querys were written just to include the name for the
> parent tag.
> can someone pls tell me if its possible to improvise..
> is it possible to use for xml auto and get CDATA..
Do you really need CDATA Sections? They are often unnecessary.
See http://xml.silmaril.ie/authors/cdata/
///Peter
|||I have to second Peter's sentiment about CDATA sections. CDATA sections are
meant for helping manual authoring of XML documents and should not be used
otherwise.
And no, FOR XML AUTO does not provide a way to generate CDATA sections. You
would have to use the EXPLICIT mode.
Best regards
Michael
"Peter Flynn" <peter.nosp@.m.silmaril.ie> wrote in message
news:3sahonFn6kgqU4@.individual.net...
> Toby wrote:
>
> Do you really need CDATA Sections? They are often unnecessary.
> See http://xml.silmaril.ie/authors/cdata/
> ///Peter
>
<X>
-<XY>
-<XYZ>
<![CDATA[ hello adkfsd ]]>
</XYZ>
-<XYZ1>
<![CDATA[ hell adkfsd ]]>
</XYZ1>
<XY>
<XY1>
...
</XY1
</X>
it is very element centric . All the data comes from a single table.
here just for mentioning the parent tag for the datas i need to write a
select.
i wrote a query which had 6 selects and got the XML.
two of the select querys were written just to include the name for the
parent tag.
can someone pls tell me if its possible to improvise..
is it possible to use for xml auto and get CDATA..
waiting for ur replies,
toby
Toby wrote:
> my xml would be like.
> <X>
> -<XY>
> -<XYZ>
> <![CDATA[ hello adkfsd ]]>
> </XYZ>
> -<XYZ1>
> <![CDATA[ hell adkfsd ]]>
> </XYZ1>
> <XY>
> <XY1>
> ...
> </XY1
> </X>
>
> it is very element centric . All the data comes from a single table.
> here just for mentioning the parent tag for the datas i need to write a
> select.
> i wrote a query which had 6 selects and got the XML.
> two of the select querys were written just to include the name for the
> parent tag.
> can someone pls tell me if its possible to improvise..
> is it possible to use for xml auto and get CDATA..
Do you really need CDATA Sections? They are often unnecessary.
See http://xml.silmaril.ie/authors/cdata/
///Peter
|||I have to second Peter's sentiment about CDATA sections. CDATA sections are
meant for helping manual authoring of XML documents and should not be used
otherwise.
And no, FOR XML AUTO does not provide a way to generate CDATA sections. You
would have to use the EXPLICIT mode.
Best regards
Michael
"Peter Flynn" <peter.nosp@.m.silmaril.ie> wrote in message
news:3sahonFn6kgqU4@.individual.net...
> Toby wrote:
>
> Do you really need CDATA Sections? They are often unnecessary.
> See http://xml.silmaril.ie/authors/cdata/
> ///Peter
>
Subscribe to:
Posts (Atom)