Help with update forms: xml

Showing posts with label xml. Show all posts

Friday, March 23, 2012

Help! Problem with XSD mapping schema

Hello,
I have a simple mapping schema and XML document for import into MSSQL. The tables will be created on import by the bulkloader.
Im getting the cryptic error message: "Schema: invalid value for 'column' on 'bl_advertiser_listing'." when I start the import. I cant figure out why this is and I have reduced the job down to a very simple state. What 'column' is this error referring to and why? Thankyou.

The XML

The Schema:

<xs Tongue Tied chema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlnsql="urnchemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="advertiser_listings" parent="bl_advertiser" parent-key="id" child="bl_advertiser_listing" child-key="id"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="listings" sql:is-constant="1">
<xs:complexType>
<xsequence>
<xs:element name="advertiser" minOccurs="0" maxOccurs="unbounded" sql:relation="bl_advertiser" sql:key-fields="id">
<xs:complexType>
<xsequence>
<xs:element name="listing" minOccurs="0" maxOccurs="unbounded" sql:relation="bl_advertiser_listing" sql:relationship="advertiser_listings">
<xs:complexType>
<xsequence>
<xs:element name="ibl-id" type="xs:integer"/>
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="id" type="xs:integer"/>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xschema>

Moving thread...

|||I worked this out, it was quite simple! <xs:element name="ibl-id" type="xs:integer"/> is invalid since the dash - is not allowed in column names.

Help! Problem with XSD mapping schema

The XML

Moving thread...

|||

I worked this out, it was quite simple! <xs:element name="ibl-id" type="xs:integer"/> is invalid since the dash - is not allowed in column names.

Help! Problem with XSD mapping schema

The XML

Moving thread...

|||I worked this out, it was quite simple! <xs:element name="ibl-id" type="xs:integer"/> is invalid since the dash - is not allowed in column names.

Wednesday, March 21, 2012

HELP! I've lost a SSAS "cube" due to a "File ==> Save as XML"

Then, the cube name became XML, listed under the "Assemblies"... and, then made the mistake of "deploy-ing"... and of course, didn't have a backup... It appears all of the recent "calculations" added to the cube are contained within the xmla file, yet the cube still looks like it is missing from the VisualStudio 2005 (VS2005).

Over in the SS Management Studio (SSMS), I tried wrapping the "Alter AllowCreate..." around the cube XML, and that seemed to work, so that now the Cube appears within the SSMS... However, I can't seem to find a way to open it up with VS2005, and see the cube name/measures, as I can see them within SSMS...

Thanks in advance for your help!

Hi Al,

If you can see the cube in SSMS then what you should be able to do is in BIDS (BI Developer Studio) go to File/Open then choose Analysis Services Database, specify the Server Name and Database name. You can put this to a new solution if you need to.

Once you click OK it will have a direct connection to the cube on the server where you can modify things.

Hope this helps,

David Botzenhart

|||

Thanks... I had proceeded to click on the XMLA file (which was actually within "Miscellaneous", instead of Assemblies), and then used "File ==> Save as Cube", which worked, for the most part... Also, apparently, I also saved the DSV as XML, which lost the relationships between the source tables... After I reconnected the table relationships, then the CUBE deployed successfully...

Thanks for the quick reply

p.s... Moral to the story, if you want to backup XMLA script file, then utilize the Deployment Wizard, and click the box to "create the script".... and/or Backup the source and/or SSAS database regularly

Friday, February 24, 2012

Help with XQuery

I am trying to query an XML Datatype column in SQL Server, but getting no data back, and I suspect the XQuery but don't know what I am doing wrong:

SELECT

nref.value('AdvTxnID[1]', 'integer') AdvTxnID,

nref.value('SvcSysCode[1]', 'varchar(2)') SvcSysCode

FROM

myData CROSS APPLY [xmldata].nodes('/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

</AdvanceTxn>

</AdvanceTxnDoc>

What am I doing wrong?

Need to declare namespace in your xquery. Two way to do it as following example:

declare @.x xml
set @.x =
N'<AdvanceTxnDoc xmlns="http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc">
<AdvanceTxn>
<AdvTxnID>4125281</AdvTxnID>
<SvcSysCode>MS</SvcSysCode>
<LoanID>8484664</LoanID>
<AdvTypeCode>P&I</AdvTypeCode>
<AdvEffDate>2005-08-25T00:00:00</AdvEffDate>
<AdvTxnAmt>.04</AdvTxnAmt>
<TxnCrtDate>2005-08-19T00:00:00</TxnCrtDate>
</AdvanceTxn>
<TxnCount>1</TxnCount>
</AdvanceTxnDoc>'

with xmlnamespaces(default 'http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc')
SELECT
nref.value('AdvTxnID[1]', 'integer') AdvTxnID,
nref.value('SvcSysCode[1]', 'varchar(2)') SvcSysCode
FROM
@.x.nodes('/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

SELECT
nref.value('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc"; AdvTxnID[1]', 'integer') AdvTxnID,
nref.value('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc"; SvcSysCode[1]', 'varchar(2)') SvcSysCode
FROM
@.x.nodes('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc";/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

|||

Worked great. Thanks!

-Kory

Help with XQuery

Hi all!
I have a SQL Server 2005 table with a XML field containing documents with
the following structure:
<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>
Via XQuery, it is possible to retrieve only the text that is contains within
a certain tag? For example, if I want the text that is surrounded by the "t"
tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in the same way,
the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT 3 TEXT 6".
Thanks in advance for the attentio.
--
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcomMarco Minerva wrote:

> I have a SQL Server 2005 table with a XML field containing documents
> with the following structure:
> <document>
> <title>Trittico di San Giovenale</title>
> <info>
> </info>
> <text>
> <ap>
> <e>
> <t>TEXT 1</t>
> TEXT 2
> <t>TEXT 3</t>
> </e>
> <t>TEXT 4</t>
> </ap>
> <ap>
> <t>TEXT 5
> <e>TEXT 6</e>
> </t>
> </ap>
> </text>
> </document>
> Via XQuery, it is possible to retrieve only the text that is contains
> within a certain tag? For example, if I want the text that is surrounded
> by the "t" tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in
> the same way, the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT
> 3 TEXT 6".
Using e.g. //e//text() you can access all descendant text nodes so
DECLARE @.x XML;
SET @.x = '<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>';
SELECT @.x.query('//e//text()') AS etext;
returns
TEXT 1
TEXT 2
TEXT 3TEXT 6
and
SELECT @.x.query('//t//text()') AS ttext;
returns
TEXT 1TEXT 3TEXT 4TEXT 5
TEXT 6
As you can see the query finds the right text nodes but includes white
space while you seem to want to strip white space but insert one space
between text nodes.
With XQuery 1.0 you could use the normalize-space and string-join
function but unfortunately the XQuery implementation in SQL server 2005
does not provide those functions.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OQpt96%23tHHA.4440@.TK2MSFTNGP06.phx.gbl...
> Marco Minerva wrote:
>
> Using e.g. //e//text() you can access all descendant text nodes so
>
> SELECT @.x.query('//e//text()') AS etext;
> returns
> TEXT 1
> TEXT 2
> TEXT 3TEXT 6
> and
> SELECT @.x.query('//t//text()') AS ttext;
> returns
> TEXT 1TEXT 3TEXT 4TEXT 5
> TEXT 6
>
> As you can see the query finds the right text nodes but includes white
> space while you seem to want to strip white space but insert one space
> between text nodes.
> With XQuery 1.0 you could use the normalize-space and string-join function
> but unfortunately the XQuery implementation in SQL server 2005 does not
> provide those functions.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Hi!
Thank you very much, it is what I was looking for!
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom

Help with XQuery

Hi all!
I have a SQL Server 2005 table with a XML field containing documents with
the following structure:
<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>
Via XQuery, it is possible to retrieve only the text that is contains within
a certain tag? For example, if I want the text that is surrounded by the "t"
tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in the same way,
the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT 3 TEXT 6".
Thanks in advance for the attentio.
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom
Marco Minerva wrote:

> I have a SQL Server 2005 table with a XML field containing documents
> with the following structure:
> <document>
> <title>Trittico di San Giovenale</title>
> <info>
> </info>
> <text>
> <ap>
> <e>
> <t>TEXT 1</t>
> TEXT 2
> <t>TEXT 3</t>
> </e>
> <t>TEXT 4</t>
> </ap>
> <ap>
> <t>TEXT 5
> <e>TEXT 6</e>
> </t>
> </ap>
> </text>
> </document>
> Via XQuery, it is possible to retrieve only the text that is contains
> within a certain tag? For example, if I want the text that is surrounded
> by the "t" tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in
> the same way, the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT
> 3 TEXT 6".
Using e.g. //e//text() you can access all descendant text nodes so
DECLARE @.x XML;
SET @.x = '<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>';
SELECT @.x.query('//e//text()') AS etext;
returns
TEXT 1
TEXT 2
TEXT 3TEXT 6
and
SELECT @.x.query('//t//text()') AS ttext;
returns
TEXT 1TEXT 3TEXT 4TEXT 5
TEXT 6
As you can see the query finds the right text nodes but includes white
space while you seem to want to strip white space but insert one space
between text nodes.
With XQuery 1.0 you could use the normalize-space and string-join
function but unfortunately the XQuery implementation in SQL server 2005
does not provide those functions.

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OQpt96%23tHHA.4440@.TK2MSFTNGP06.phx.gbl...
> Marco Minerva wrote:
>
> Using e.g. //e//text() you can access all descendant text nodes so
>
> SELECT @.x.query('//e//text()') AS etext;
> returns
> TEXT 1
> TEXT 2
> TEXT 3TEXT 6
> and
> SELECT @.x.query('//t//text()') AS ttext;
> returns
> TEXT 1TEXT 3TEXT 4TEXT 5
> TEXT 6
>
> As you can see the query finds the right text nodes but includes white
> space while you seem to want to strip white space but insert one space
> between text nodes.
> With XQuery 1.0 you could use the normalize-space and string-join function
> but unfortunately the XQuery implementation in SQL server 2005 does not
> provide those functions.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Hi!
Thank you very much, it is what I was looking for!
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom

Help with XML to Excel

Hi All,
I have a fairly complex XML file which I wanted to load to a table
using BulkLoad. However, BulkLoad has limitations when it comes to
error in the XML file (if data is bad, it stops).
After doing some research, I found out that if I have a file, say,
Books.xml and I open it in Excel (right click, open with Excel), it
does exactly what I want. I have the entire XML into my excel sheet,
with all the columns headers. It requires no schema at all.
My question is how Excel does this? How does it convert the XML into
the Excel sheet? If I can get that component of Excel that does it,
that will help me a lot. The reason is I do not want to use Excel while
I am loading my XML file into the table. I just want to know how the
Excel does it without schema, and I can do the same to export it into
the table as is.
If Bulk Load did not have the limitation, I would go for Bulk Load, but
the above conversion of xml to excel is even better if I can figure out
how it is done.
I am using Windows XP with MS Excel 2003.
Please help,
Thanks,
ChrisThere are several tools that can help you here. Part of GotDotNet, there is
a tool that will generate an XSD schema for your Xml document. The tool is
here:
http://apps.gotdotnet.com/xmltools/xsdinference/
You can later on apply couple of annotations in the XSD to make Bulkload
work with the generated schema. This should be pretty straightforward since
you want to generate a single table from your Xml Document. Just put
sql:is-constant="1" on the container elements if you have any and you are
good to go.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
<undoredo@.gmail.com> wrote in message
news:1113421813.353131.241690@.o13g2000cwo.googlegroups.com...
> Hi All,
> I have a fairly complex XML file which I wanted to load to a table
> using BulkLoad. However, BulkLoad has limitations when it comes to
> error in the XML file (if data is bad, it stops).
> After doing some research, I found out that if I have a file, say,
> Books.xml and I open it in Excel (right click, open with Excel), it
> does exactly what I want. I have the entire XML into my excel sheet,
> with all the columns headers. It requires no schema at all.
> My question is how Excel does this? How does it convert the XML into
> the Excel sheet? If I can get that component of Excel that does it,
> that will help me a lot. The reason is I do not want to use Excel while
> I am loading my XML file into the table. I just want to know how the
> Excel does it without schema, and I can do the same to export it into
> the table as is.
> If Bulk Load did not have the limitation, I would go for Bulk Load, but
> the above conversion of xml to excel is even better if I can figure out
> how it is done.
> I am using Windows XP with MS Excel 2003.
> Please help,
> Thanks,
> Chris
>

Help with XML to Excel

Hi All,
I have a fairly complex XML file which I wanted to load to a table
using BulkLoad. However, BulkLoad has limitations when it comes to
error in the XML file (if data is bad, it stops).
After doing some research, I found out that if I have a file, say,
Books.xml and I open it in Excel (right click, open with Excel), it
does exactly what I want. I have the entire XML into my excel sheet,
with all the columns headers. It requires no schema at all.
My question is how Excel does this? How does it convert the XML into
the Excel sheet? If I can get that component of Excel that does it,
that will help me a lot. The reason is I do not want to use Excel while
I am loading my XML file into the table. I just want to know how the
Excel does it without schema, and I can do the same to export it into
the table as is.
If Bulk Load did not have the limitation, I would go for Bulk Load, but
the above conversion of xml to excel is even better if I can figure out
how it is done.
I am using Windows XP with MS Excel 2003.
Please help,
Thanks,
Chris
There are several tools that can help you here. Part of GotDotNet, there is
a tool that will generate an XSD schema for your Xml document. The tool is
here:
http://apps.gotdotnet.com/xmltools/xsdinference/
You can later on apply couple of annotations in the XSD to make Bulkload
work with the generated schema. This should be pretty straightforward since
you want to generate a single table from your Xml Document. Just put
sql:is-constant="1" on the container elements if you have any and you are
good to go.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
<undoredo@.gmail.com> wrote in message
news:1113421813.353131.241690@.o13g2000cwo.googlegr oups.com...
> Hi All,
> I have a fairly complex XML file which I wanted to load to a table
> using BulkLoad. However, BulkLoad has limitations when it comes to
> error in the XML file (if data is bad, it stops).
> After doing some research, I found out that if I have a file, say,
> Books.xml and I open it in Excel (right click, open with Excel), it
> does exactly what I want. I have the entire XML into my excel sheet,
> with all the columns headers. It requires no schema at all.
> My question is how Excel does this? How does it convert the XML into
> the Excel sheet? If I can get that component of Excel that does it,
> that will help me a lot. The reason is I do not want to use Excel while
> I am loading my XML file into the table. I just want to know how the
> Excel does it without schema, and I can do the same to export it into
> the table as is.
> If Bulk Load did not have the limitation, I would go for Bulk Load, but
> the above conversion of xml to excel is even better if I can figure out
> how it is done.
> I am using Windows XP with MS Excel 2003.
> Please help,
> Thanks,
> Chris
>

Help with XML Bulk Load

Hi All,
I am trying to load an XML file to SQL Server using SQLXML3.0 Bulk Load.
The XML has several child tags. Here is the sample XML.
<?xml version="1.0"?>
<batch>
<batchheader>
<merchant>Merchant Name</merchant>
<batchnumber>7735</batchnumber>
<date>2005-03-26</date>
<time>144407</time>
</batchheader>
<order>
<ordernumber>240420012591</ordernumber>
<orderdate>20050325</orderdate>
<POnumber>123456</POnumber>
</order>
<BillingAddress>
<Address1>123 Main Street</Address1>
<Address2>Apt 3</Address2>
<City>Los Angeles</City>
<State>CA</State>
<Zip>90007</Zip>
<BillingAddress>
</batch>
The Schema defined is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="merchant" type="xsd:string" sql:field="Merchant">
</xsd:element>
<xsd:element name="batchnumber" type="xsd:string"
sql:field="BatchNumber">
</xsd:element>
<xsd:element name="date" type="xsd:string" sql:field="BatchDate">
</xsd:element>
<xsd:element name="time" type="xsd:string" sql:field="BatchTime">
</xsd:element>
<xsd:element name="batchheader">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="merchant"/>
<xsd:element ref="batchnumber"/>
<xsd:element ref="date"/>
<xsd:element ref="time"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="ordernumber" type="xsd:string"
sql:field="OrderNumber">
</xsd:element>
<xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
</xsd:element>
<xsd:element name="POnumber" type="xsd:string" sql:field="PONumber">
</xsd:element>
<xsd:element name="order">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="Address1" type="xsd:string" sql:field="Address1">
</xsd:element>
<xsd:element name="Address2" type="xsd:string" sql:field="Address2">
</xsd:element>
<xsd:element name="City" type="xsd:string" sql:field="City">
</xsd:element>
<xsd:element name="State" type="xsd:string" sql:field="State">
</xsd:element>
<xsd:element name="Zip" type="xsd:string" sql:field="Zip">
</xsd:element>
<xsd:element name="BillingAddress">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="Address1"/>
<xsd:element ref="Address2"/>
<xsd:element ref="City"/>
<xsd:element ref="State"/>
<xsd:element ref="Zip"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader"/>
<xsd:element ref="order"/>
<xsd:element ref="BillingAddress"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My problem is that I am loading ALL of the element in one table. I have a
table 'xmlStaging' already defined with all of the simple elements. But
when I load the above file, I get the following error:
*** Schema: relationship expected on 'batchheader' ***
If I use the sql:relation="xmlStaging" on <batchheader>, <order> and
<BillingAddress> separately, but not on the batch, I get two records
inserted into the table, that is, it treats <batchheader> as one row with
NULLS on <order> elements, and 2nd row has elements from <order> with NULLS on
<batchherader>, .. so for Billing Address
All I want is that my XML docuemnt above is inserted into only one row.
The above XML document will have multiple records, that is, it will be
something like...
<batch>
<batchheader>
<element... >
<element... >
<element... >
</batchheader>
<order>
<element... >
<element... >
<element... >
</order>
<BillingAddress>
<element... >
<element... >
<element... > ...
</BillingAddress>
<order>
<element... >
<element... >
<element... >
</order>
<BillingAddress>
<element... >
<element... >
<element... > ...
</BillingAddress>
<order>
<element... >
<element... >
<element... >
</order>
...
...
...
</batch>
Please help,
Thanks,
Aleem
Please try putting sql:is-constant="1" annotation on 'batchheader' element
declaration in XSD like this:
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader" sql:is-constant="1"/>
<xsd:element ref="order"/>
<xsd:element ref="BillingAddress"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
That should fix your problem. Apply the same technique to other elements if
you get similar errors.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aleem Rana" <arana@.aludra.usc.edu> wrote in message
news:Pine.GSO.4.33.0504121559290.13331-100000@.aludra.usc.edu...
> Hi All,
> I am trying to load an XML file to SQL Server using SQLXML3.0 Bulk Load.
> The XML has several child tags. Here is the sample XML.
>
> <?xml version="1.0"?>
> <batch>
> <batchheader>
> <merchant>Merchant Name</merchant>
> <batchnumber>7735</batchnumber>
> <date>2005-03-26</date>
> <time>144407</time>
> </batchheader>
> <order>
> <ordernumber>240420012591</ordernumber>
> <orderdate>20050325</orderdate>
> <POnumber>123456</POnumber>
> </order>
> <BillingAddress>
> <Address1>123 Main Street</Address1>
> <Address2>Apt 3</Address2>
> <City>Los Angeles</City>
> <State>CA</State>
> <Zip>90007</Zip>
> <BillingAddress>
> </batch>
>
> The Schema defined is:
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>
> <xsd:element name="merchant" type="xsd:string" sql:field="Merchant">
> </xsd:element>
> <xsd:element name="batchnumber" type="xsd:string"
> sql:field="BatchNumber">
> </xsd:element>
> <xsd:element name="date" type="xsd:string" sql:field="BatchDate">
> </xsd:element>
> <xsd:element name="time" type="xsd:string" sql:field="BatchTime">
> </xsd:element>
>
> <xsd:element name="batchheader">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="merchant"/>
> <xsd:element ref="batchnumber"/>
> <xsd:element ref="date"/>
> <xsd:element ref="time"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="ordernumber" type="xsd:string"
> sql:field="OrderNumber">
> </xsd:element>
> <xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
> </xsd:element>
> <xsd:element name="POnumber" type="xsd:string" sql:field="PONumber">
> </xsd:element>
> <xsd:element name="order">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
>
> <xsd:element name="Address1" type="xsd:string" sql:field="Address1">
> </xsd:element>
> <xsd:element name="Address2" type="xsd:string" sql:field="Address2">
> </xsd:element>
> <xsd:element name="City" type="xsd:string" sql:field="City">
> </xsd:element>
> <xsd:element name="State" type="xsd:string" sql:field="State">
> </xsd:element>
> <xsd:element name="Zip" type="xsd:string" sql:field="Zip">
> </xsd:element>
>
> <xsd:element name="BillingAddress">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="Address1"/>
> <xsd:element ref="Address2"/>
> <xsd:element ref="City"/>
> <xsd:element ref="State"/>
> <xsd:element ref="Zip"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader"/>
> <xsd:element ref="order"/>
> <xsd:element ref="BillingAddress"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My problem is that I am loading ALL of the element in one table. I have a
> table 'xmlStaging' already defined with all of the simple elements. But
> when I load the above file, I get the following error:
> *** Schema: relationship expected on 'batchheader' ***
> If I use the sql:relation="xmlStaging" on <batchheader>, <order> and
> <BillingAddress> separately, but not on the batch, I get two records
> inserted into the table, that is, it treats <batchheader> as one row with
> NULLS on <order> elements, and 2nd row has elements from <order> with
NULLS on
> <batchherader>, .. so for Billing Address
>
> All I want is that my XML docuemnt above is inserted into only one row.
> The above XML document will have multiple records, that is, it will be
> something like...
> <batch>
> <batchheader>
> <element... >
> <element... >
> <element... >
> </batchheader>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> <BillingAddress>
> <element... >
> <element... >
> <element... > ...
> </BillingAddress>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> <BillingAddress>
> <element... >
> <element... >
> <element... > ...
> </BillingAddress>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> ...
> ...
> ...
> </batch>
>
> Please help,
> Thanks,
> Aleem
>
>
>
|||Hi Bertan,
I tried what you suggested, but got the following error:
<Source>Microsoft OLE DB Provider for SQL
Server</Source><Description><![CDATA[Invalid object name 'batchheader'.]]>
...
Let me write down my complete XML and Schema.
XML:
<?xml version="1.0"?>
<batch>
<batchheader>
<merchant>Merchant Name</merchant>
<batchnumber>7735</batchnumber>
<date>2005-03-26</date>
<time>144407</time>
</batchheader>
<order>
<ordernumber>240420012591</ordernumber>
<orderdate>20050325</orderdate>
<POnumber>123456</POnumber>
</order>
</batch>
The Schema:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:simpleType name ="FiftyCharacterNonNullString">
<xsd:restriction base="xsd:string">
<xsd:minLength value="1"/>
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:element name="merchant" type="FiftyCharacterNonNullString"
sql:field="Merchant">
</xsd:element>
<xsd:element name="batchnumber" type="FiftyCharacterNonNullString"
sql:field="BatchNumber">
</xsd:element>
<xsd:element name="date" type="xsd:string" sql:field="BatchDate">
</xsd:element>
<xsd:element name="time" type="xsd:string" sql:field="BatchTime">
</xsd:element>
<xsd:element name="batchheader">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="merchant"/>
<xsd:element ref="batchnumber"/>
<xsd:element ref="date"/>
<xsd:element ref="time"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="ordernumber" type="FiftyCharacterNonNullString"
sql:field="OrderNumber">
</xsd:element>
<xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
</xsd:element>
<xsd:element name="POnumber" type="FiftyCharacterNonNullString"
sql:field="PONumber">
</xsd:element>
<xsd:element name="order">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader" sql:is-constant="1"/>
<xsd:element ref="order" sql:is-constant="1"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
*****
I have validated the Schema using Stylus Studio XML and there are no
errors.
Can you please help why am I getting the error? My xml file has the
batchheader element in it, and I also have the annotation
sql:is-constant="1" as you suggested.
Please help,
Thanks a lot,
Aleem
On Wed, 13 Apr 2005, Bertan ARI [MSFT] wrote:

> Please try putting sql:is-constant="1" annotation on 'batchheader' element
> declaration in XSD like this:
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader" sql:is-constant="1"/>
> <xsd:element ref="order"/>
> <xsd:element ref="BillingAddress"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> That should fix your problem. Apply the same technique to other elements if
> you get similar errors.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Aleem Rana" <arana@.aludra.usc.edu> wrote in message
> news:Pine.GSO.4.33.0504121559290.13331-100000@.aludra.usc.edu...
> NULLS on
>
>
|||I fixed one problem. The annotation sql:is-constant="1" needs to be
defined at both places, when declaring the element and when refering it to
as well. But now I have a new problem which I can not find a way to solve.
It may be my schema, but not sure. Here it is.
If my XML is something like,
<order>
<salestax>
<amount>1.23</amount>
<rate>8.25</rate>
</salestax>
<itemlist>
<item>
<name>DVD</name>
<price>12.00</price>
<partnumber>12345</partnumber>
</item>
<item>
<name>DVD</name>
<price>12.00</price>
<partnumber>12345</partnumber>
</item>
... more <item> </item>
</itemlist>
</order>
<order>
....
....
</order>
In my Schema, I have relation on <order .. sql:relation="xmlStaging">
and all other has <element ... sql:is-constant="1">
But when it comes to inserting Item in the table, it fails. The error I
get is that the ItemName column was already found; make sure that no two
columns has the same schema. What I need is when it finds a second Item,
it inserts in a new row.
Part of my Schema is below:
<xsd:element name="type" type="xsd:string" sql:field="Type">
</xsd:element>
<xsd:element name="sku" type="xsd:string" sql:field="SKU">
</xsd:element>
<xsd:element name="partnumber" type="xsd:string" sql:field="Number">
</xsd:element>
<xsd:element name="description" type="xsd:string"
sql:field="Description">
</xsd:element>
<xsd:element name="unitprice" type="xsd:string" sql:field="UnitPrice">
</xsd:element>
<xsd:element name="quantity" type="xsd:string" sql:field="Quantity">
</xsd:element>
<xsd:element name="item" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="type"/>
<xsd:element ref="sku"/>
<xsd:element ref="partnumber"/>
<xsd:element ref="description"/>
<xsd:element ref="unitprice"/>
<xsd:element ref="quantity"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="lineitemlist" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element ref="item"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
......
......
......
<xsd:element name="order" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
<xsd:element ref="salestax" sql:is-constant="1"/>
<xsd:element ref="shippingandhandling" sql:is-constant="1"/>
<xsd:element ref="lineitemlist" sql:is-constant="1"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
Please help with the problem. Is there something in the schema I need to
change? How can I make sure that anytime it finds a new <item>, it should
go to a new row.
Any help would be really appreciated,
Thanks,
Aleem
On Wed, 13 Apr 2005, Aleem Rana wrote:

> Hi Bertan,
> I tried what you suggested, but got the following error:
> <Source>Microsoft OLE DB Provider for SQL
> Server</Source><Description><![CDATA[Invalid object name 'batchheader'.]]>
> ...
> Let me write down my complete XML and Schema.
> XML:
> <?xml version="1.0"?>
> <batch>
> <batchheader>
> <merchant>Merchant Name</merchant>
> <batchnumber>7735</batchnumber>
> <date>2005-03-26</date>
> <time>144407</time>
> </batchheader>
> <order>
> <ordernumber>240420012591</ordernumber>
> <orderdate>20050325</orderdate>
> <POnumber>123456</POnumber>
> </order>
> </batch>
>
> The Schema:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>
> <xsd:simpleType name ="FiftyCharacterNonNullString">
> <xsd:restriction base="xsd:string">
> <xsd:minLength value="1"/>
> <xsd:maxLength value="50"/>
> </xsd:restriction>
> </xsd:simpleType>
>
>
> <xsd:element name="merchant" type="FiftyCharacterNonNullString"
> sql:field="Merchant">
> </xsd:element>
> <xsd:element name="batchnumber" type="FiftyCharacterNonNullString"
> sql:field="BatchNumber">
> </xsd:element>
> <xsd:element name="date" type="xsd:string" sql:field="BatchDate">
> </xsd:element>
> <xsd:element name="time" type="xsd:string" sql:field="BatchTime">
> </xsd:element>
>
> <xsd:element name="batchheader">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="merchant"/>
> <xsd:element ref="batchnumber"/>
> <xsd:element ref="date"/>
> <xsd:element ref="time"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="ordernumber" type="FiftyCharacterNonNullString"
> sql:field="OrderNumber">
> </xsd:element>
> <xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
> </xsd:element>
> <xsd:element name="POnumber" type="FiftyCharacterNonNullString"
> sql:field="PONumber">
> </xsd:element>
>
> <xsd:element name="order">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader" sql:is-constant="1"/>
> <xsd:element ref="order" sql:is-constant="1"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> *****
> I have validated the Schema using Stylus Studio XML and there are no
> errors.
> Can you please help why am I getting the error? My xml file has the
> batchheader element in it, and I also have the annotation
> sql:is-constant="1" as you suggested.
> Please help,
> Thanks a lot,
> Aleem
>
>
> On Wed, 13 Apr 2005, Bertan ARI [MSFT] wrote:
>
>
|||Unfortunately, you cannot do that. We do not support denormalized tables.
You need to create a second table and map the Items element to this second
table.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aleem Rana" <arana@.aludra.usc.edu> wrote in message
news:Pine.GSO.4.33.0504131838130.4478-100000@.aludra.usc.edu...[vbcol=seagreen]
> I fixed one problem. The annotation sql:is-constant="1" needs to be
> defined at both places, when declaring the element and when refering it to
> as well. But now I have a new problem which I can not find a way to solve.
> It may be my schema, but not sure. Here it is.
>
> If my XML is something like,
> <order>
> <salestax>
> <amount>1.23</amount>
> <rate>8.25</rate>
> </salestax>
> <itemlist>
> <item>
> <name>DVD</name>
> <price>12.00</price>
> <partnumber>12345</partnumber>
> </item>
> <item>
> <name>DVD</name>
> <price>12.00</price>
> <partnumber>12345</partnumber>
> </item>
> ... more <item> </item>
> </itemlist>
> </order>
> <order>
> ...
> ...
> </order>
>
> In my Schema, I have relation on <order .. sql:relation="xmlStaging">
> and all other has <element ... sql:is-constant="1">
> But when it comes to inserting Item in the table, it fails. The error I
> get is that the ItemName column was already found; make sure that no two
> columns has the same schema. What I need is when it finds a second Item,
> it inserts in a new row.
> Part of my Schema is below:
>
> <xsd:element name="type" type="xsd:string" sql:field="Type">
> </xsd:element>
> <xsd:element name="sku" type="xsd:string" sql:field="SKU">
> </xsd:element>
> <xsd:element name="partnumber" type="xsd:string" sql:field="Number">
> </xsd:element>
> <xsd:element name="description" type="xsd:string"
> sql:field="Description">
> </xsd:element>
> <xsd:element name="unitprice" type="xsd:string" sql:field="UnitPrice">
> </xsd:element>
> <xsd:element name="quantity" type="xsd:string" sql:field="Quantity">
> </xsd:element>
>
> <xsd:element name="item" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="type"/>
> <xsd:element ref="sku"/>
> <xsd:element ref="partnumber"/>
> <xsd:element ref="description"/>
> <xsd:element ref="unitprice"/>
> <xsd:element ref="quantity"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="lineitemlist" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence minOccurs="0" maxOccurs="unbounded">
> <xsd:element ref="item"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> .....
> .....
> .....
>
> <xsd:element name="order" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> <xsd:element ref="salestax" sql:is-constant="1"/>
> <xsd:element ref="shippingandhandling" sql:is-constant="1"/>
> <xsd:element ref="lineitemlist" sql:is-constant="1"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> Please help with the problem. Is there something in the schema I need to
> change? How can I make sure that anytime it finds a new <item>, it should
> go to a new row.
>
> Any help would be really appreciated,
> Thanks,
> Aleem
>
>
> On Wed, 13 Apr 2005, Aleem Rana wrote:
'batchheader'.]]>[vbcol=seagreen]
element[vbcol=seagreen]
elements if[vbcol=seagreen]
rights.[vbcol=seagreen]
Load.[vbcol=seagreen]
sql:field="Merchant">[vbcol=seagreen]
sql:field="OrderDate">[vbcol=seagreen]
sql:field="PONumber">[vbcol=seagreen]
sql:field="Address1">[vbcol=seagreen]
sql:field="Address2">[vbcol=seagreen]
have a[vbcol=seagreen]
But[vbcol=seagreen]
with[vbcol=seagreen]
with[vbcol=seagreen]
row.[vbcol=seagreen]
be
>

Help with XML Bulk Load

Hi All,
I am trying to load an XML file to SQL Server using SQLXML3.0 Bulk Load.
The XML has several child tags. Here is the sample XML.
<?xml version="1.0"?>
<batch>
<batchheader>
<merchant>Merchant Name</merchant>
<batchnumber>7735</batchnumber>
<date>2005-03-26</date>
<time>144407</time>
</batchheader>
<order>
<ordernumber>240420012591</ordernumber>
<orderdate>20050325</orderdate>
<POnumber>123456</POnumber>
</order>
<BillingAddress>
<Address1>123 Main Street</Address1>
<Address2>Apt 3</Address2>
<City>Los Angeles</City>
<State>CA</State>
<Zip>90007</Zip>
<BillingAddress>
</batch>
The Schema defined is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="merchant" type="xsd:string" sql:field="Merchant">
</xsd:element>
<xsd:element name="batchnumber" type="xsd:string"
sql:field="BatchNumber">
</xsd:element>
<xsd:element name="date" type="xsd:string" sql:field="BatchDate">
</xsd:element>
<xsd:element name="time" type="xsd:string" sql:field="BatchTime">
</xsd:element>
<xsd:element name="batchheader">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="merchant"/>
<xsd:element ref="batchnumber"/>
<xsd:element ref="date"/>
<xsd:element ref="time"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="ordernumber" type="xsd:string"
sql:field="OrderNumber">
</xsd:element>
<xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
</xsd:element>
<xsd:element name="POnumber" type="xsd:string" sql:field="PONumber">
</xsd:element>
<xsd:element name="order">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="Address1" type="xsd:string" sql:field="Address1">
</xsd:element>
<xsd:element name="Address2" type="xsd:string" sql:field="Address2">
</xsd:element>
<xsd:element name="City" type="xsd:string" sql:field="City">
</xsd:element>
<xsd:element name="State" type="xsd:string" sql:field="State">
</xsd:element>
<xsd:element name="Zip" type="xsd:string" sql:field="Zip">
</xsd:element>
<xsd:element name="BillingAddress">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="Address1"/>
<xsd:element ref="Address2"/>
<xsd:element ref="City"/>
<xsd:element ref="State"/>
<xsd:element ref="Zip"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader"/>
<xsd:element ref="order"/>
<xsd:element ref="BillingAddress"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My problem is that I am loading ALL of the element in one table. I have a
table 'xmlStaging' already defined with all of the simple elements. But
when I load the above file, I get the following error:
*** Schema: relationship expected on 'batchheader' ***
If I use the sql:relation="xmlStaging" on <batchheader>, <order> and
<BillingAddress> separately, but not on the batch, I get two records
inserted into the table, that is, it treats <batchheader> as one row with
NULLS on <order> elements, and 2nd row has elements from <order> with NULLS
on
<batchherader>, .. so for Billing Address
All I want is that my XML docuemnt above is inserted into only one row.
The above XML document will have multiple records, that is, it will be
something like...
<batch>
<batchheader>
<element... >
<element... >
<element... >
</batchheader>
<order>
<element... >
<element... >
<element... >
</order>
<BillingAddress>
<element... >
<element... >
<element... > ...
</BillingAddress>
<order>
<element... >
<element... >
<element... >
</order>
<BillingAddress>
<element... >
<element... >
<element... > ...
</BillingAddress>
<order>
<element... >
<element... >
<element... >
</order>
...
...
...
</batch>
Please help,
Thanks,
AleemPlease try putting sql:is-constant="1" annotation on 'batchheader' element
declaration in XSD like this:
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader" sql:is-constant="1"/>
<xsd:element ref="order"/>
<xsd:element ref="BillingAddress"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
That should fix your problem. Apply the same technique to other elements if
you get similar errors.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aleem Rana" <arana@.aludra.usc.edu> wrote in message
news:Pine.GSO.4.33.0504121559290.13331-100000@.aludra.usc.edu...
> Hi All,
> I am trying to load an XML file to SQL Server using SQLXML3.0 Bulk Load.
> The XML has several child tags. Here is the sample XML.
>
> <?xml version="1.0"?>
> <batch>
> <batchheader>
> <merchant>Merchant Name</merchant>
> <batchnumber>7735</batchnumber>
> <date>2005-03-26</date>
> <time>144407</time>
> </batchheader>
> <order>
> <ordernumber>240420012591</ordernumber>
> <orderdate>20050325</orderdate>
> <POnumber>123456</POnumber>
> </order>
> <BillingAddress>
> <Address1>123 Main Street</Address1>
> <Address2>Apt 3</Address2>
> <City>Los Angeles</City>
> <State>CA</State>
> <Zip>90007</Zip>
> <BillingAddress>
> </batch>
>
> The Schema defined is:
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>
> <xsd:element name="merchant" type="xsd:string" sql:field="Merchant">
> </xsd:element>
> <xsd:element name="batchnumber" type="xsd:string"
> sql:field="BatchNumber">
> </xsd:element>
> <xsd:element name="date" type="xsd:string" sql:field="BatchDate">
> </xsd:element>
> <xsd:element name="time" type="xsd:string" sql:field="BatchTime">
> </xsd:element>
>
> <xsd:element name="batchheader">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="merchant"/>
> <xsd:element ref="batchnumber"/>
> <xsd:element ref="date"/>
> <xsd:element ref="time"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="ordernumber" type="xsd:string"
> sql:field="OrderNumber">
> </xsd:element>
> <xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
> </xsd:element>
> <xsd:element name="POnumber" type="xsd:string" sql:field="PONumber">
> </xsd:element>
> <xsd:element name="order">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
>
> <xsd:element name="Address1" type="xsd:string" sql:field="Address1">
> </xsd:element>
> <xsd:element name="Address2" type="xsd:string" sql:field="Address2">
> </xsd:element>
> <xsd:element name="City" type="xsd:string" sql:field="City">
> </xsd:element>
> <xsd:element name="State" type="xsd:string" sql:field="State">
> </xsd:element>
> <xsd:element name="Zip" type="xsd:string" sql:field="Zip">
> </xsd:element>
>
> <xsd:element name="BillingAddress">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="Address1"/>
> <xsd:element ref="Address2"/>
> <xsd:element ref="City"/>
> <xsd:element ref="State"/>
> <xsd:element ref="Zip"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader"/>
> <xsd:element ref="order"/>
> <xsd:element ref="BillingAddress"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My problem is that I am loading ALL of the element in one table. I have a
> table 'xmlStaging' already defined with all of the simple elements. But
> when I load the above file, I get the following error:
> *** Schema: relationship expected on 'batchheader' ***
> If I use the sql:relation="xmlStaging" on <batchheader>, <order> and
> <BillingAddress> separately, but not on the batch, I get two records
> inserted into the table, that is, it treats <batchheader> as one row with
> NULLS on <order> elements, and 2nd row has elements from <order> with
NULLS on
> <batchherader>, .. so for Billing Address
>
> All I want is that my XML docuemnt above is inserted into only one row.
> The above XML document will have multiple records, that is, it will be
> something like...
> <batch>
> <batchheader>
> <element... >
> <element... >
> <element... >
> </batchheader>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> <BillingAddress>
> <element... >
> <element... >
> <element... > ...
> </BillingAddress>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> <BillingAddress>
> <element... >
> <element... >
> <element... > ...
> </BillingAddress>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> ...
> ...
> ...
> </batch>
>
> Please help,
> Thanks,
> Aleem
>
>
>|||Hi Bertan,
I tried what you suggested, but got the following error:
<Source>Microsoft OLE DB Provider for SQL
Server</Source><Description><![CDATA[Invalid object name 'batchheader'.]]>
...
Let me write down my complete XML and Schema.
XML:
<?xml version="1.0"?>
<batch>
<batchheader>
<merchant>Merchant Name</merchant>
<batchnumber>7735</batchnumber>
<date>2005-03-26</date>
<time>144407</time>
</batchheader>
<order>
<ordernumber>240420012591</ordernumber>
<orderdate>20050325</orderdate>
<POnumber>123456</POnumber>
</order>
</batch>
The Schema:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:simpleType name ="FiftyCharacterNonNullString">
<xsd:restriction base="xsd:string">
<xsd:minLength value="1"/>
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:element name="merchant" type="FiftyCharacterNonNullString"
sql:field="Merchant">
</xsd:element>
<xsd:element name="batchnumber" type="FiftyCharacterNonNullString"
sql:field="BatchNumber">
</xsd:element>
<xsd:element name="date" type="xsd:string" sql:field="BatchDate">
</xsd:element>
<xsd:element name="time" type="xsd:string" sql:field="BatchTime">
</xsd:element>
<xsd:element name="batchheader">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="merchant"/>
<xsd:element ref="batchnumber"/>
<xsd:element ref="date"/>
<xsd:element ref="time"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="ordernumber" type="FiftyCharacterNonNullString"
sql:field="OrderNumber">
</xsd:element>
<xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
</xsd:element>
<xsd:element name="POnumber" type="FiftyCharacterNonNullString"
sql:field="PONumber">
</xsd:element>
<xsd:element name="order">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader" sql:is-constant="1"/>
<xsd:element ref="order" sql:is-constant="1"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
*****
I have validated the Schema using Stylus Studio XML and there are no
errors.
Can you please help why am I getting the error? My xml file has the
batchheader element in it, and I also have the annotation
sql:is-constant="1" as you suggested.
Please help,
Thanks a lot,
Aleem
On Wed, 13 Apr 2005, Bertan ARI [MSFT] wrote:

> Please try putting sql:is-constant="1" annotation on 'batchheader' element
> declaration in XSD like this:
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader" sql:is-constant="1"/>
> <xsd:element ref="order"/>
> <xsd:element ref="BillingAddress"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> That should fix your problem. Apply the same technique to other elements i
f
> you get similar errors.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Aleem Rana" <arana@.aludra.usc.edu> wrote in message
> news:Pine.GSO.4.33.0504121559290.13331-100000@.aludra.usc.edu...
> NULLS on
>
>|||I fixed one problem. The annotation sql:is-constant="1" needs to be
defined at both places, when declaring the element and when refering it to
as well. But now I have a new problem which I can not find a way to solve.
It may be my schema, but not sure. Here it is.
If my XML is something like,
<order>
<salestax>
<amount>1.23</amount>
<rate>8.25</rate>
</salestax>
<itemlist>
<item>
<name>DVD</name>
<price>12.00</price>
<partnumber>12345</partnumber>
</item>
<item>
<name>DVD</name>
<price>12.00</price>
<partnumber>12345</partnumber>
</item>
... more <item> </item>
</itemlist>
</order>
<order>
...
...
</order>
In my Schema, I have relation on <order .. sql:relation="xmlStaging">
and all other has <element ... sql:is-constant="1">
But when it comes to inserting Item in the table, it fails. The error I
get is that the ItemName column was already found; make sure that no two
columns has the same schema. What I need is when it finds a second Item,
it inserts in a new row.
Part of my Schema is below:
<xsd:element name="type" type="xsd:string" sql:field="Type">
</xsd:element>
<xsd:element name="sku" type="xsd:string" sql:field="SKU">
</xsd:element>
<xsd:element name="partnumber" type="xsd:string" sql:field="Number">
</xsd:element>
<xsd:element name="description" type="xsd:string"
sql:field="Description">
</xsd:element>
<xsd:element name="unitprice" type="xsd:string" sql:field="UnitPrice">
</xsd:element>
<xsd:element name="quantity" type="xsd:string" sql:field="Quantity">
</xsd:element>
<xsd:element name="item" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="type"/>
<xsd:element ref="sku"/>
<xsd:element ref="partnumber"/>
<xsd:element ref="description"/>
<xsd:element ref="unitprice"/>
<xsd:element ref="quantity"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="lineitemlist" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element ref="item"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
.....
.....
.....
<xsd:element name="order" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
<xsd:element ref="salestax" sql:is-constant="1"/>
<xsd:element ref="shippingandhandling" sql:is-constant="1"/>
<xsd:element ref="lineitemlist" sql:is-constant="1"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
Please help with the problem. Is there something in the schema I need to
change? How can I make sure that anytime it finds a new <item>, it should
go to a new row.
Any help would be really appreciated,
Thanks,
Aleem
On Wed, 13 Apr 2005, Aleem Rana wrote:

> Hi Bertan,
> I tried what you suggested, but got the following error:
> <Source>Microsoft OLE DB Provider for SQL
> Server</Source><Description><![CDATA[Invalid object name 'batchheader'.]]>
> ...
> Let me write down my complete XML and Schema.
> XML:
> <?xml version="1.0"?>
> <batch>
> <batchheader>
> <merchant>Merchant Name</merchant>
> <batchnumber>7735</batchnumber>
> <date>2005-03-26</date>
> <time>144407</time>
> </batchheader>
> <order>
> <ordernumber>240420012591</ordernumber>
> <orderdate>20050325</orderdate>
> <POnumber>123456</POnumber>
> </order>
> </batch>
>
> The Schema:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>
> <xsd:simpleType name ="FiftyCharacterNonNullString">
> <xsd:restriction base="xsd:string">
> <xsd:minLength value="1"/>
> <xsd:maxLength value="50"/>
> </xsd:restriction>
> </xsd:simpleType>
>
>
> <xsd:element name="merchant" type="FiftyCharacterNonNullString"
> sql:field="Merchant">
> </xsd:element>
> <xsd:element name="batchnumber" type="FiftyCharacterNonNullString"
> sql:field="BatchNumber">
> </xsd:element>
> <xsd:element name="date" type="xsd:string" sql:field="BatchDate">
> </xsd:element>
> <xsd:element name="time" type="xsd:string" sql:field="BatchTime">
> </xsd:element>
>
> <xsd:element name="batchheader">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="merchant"/>
> <xsd:element ref="batchnumber"/>
> <xsd:element ref="date"/>
> <xsd:element ref="time"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="ordernumber" type="FiftyCharacterNonNullString"
> sql:field="OrderNumber">
> </xsd:element>
> <xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
> </xsd:element>
> <xsd:element name="POnumber" type="FiftyCharacterNonNullString"
> sql:field="PONumber">
> </xsd:element>
>
> <xsd:element name="order">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader" sql:is-constant="1"/>
> <xsd:element ref="order" sql:is-constant="1"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> *****
> I have validated the Schema using Stylus Studio XML and there are no
> errors.
> Can you please help why am I getting the error? My xml file has the
> batchheader element in it, and I also have the annotation
> sql:is-constant="1" as you suggested.
> Please help,
> Thanks a lot,
> Aleem
>
>
> On Wed, 13 Apr 2005, Bertan ARI [MSFT] wrote:
>
>|||Unfortunately, you cannot do that. We do not support denormalized tables.
You need to create a second table and map the Items element to this second
table.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aleem Rana" <arana@.aludra.usc.edu> wrote in message
news:Pine.GSO.4.33.0504131838130.4478-100000@.aludra.usc.edu...
> I fixed one problem. The annotation sql:is-constant="1" needs to be
> defined at both places, when declaring the element and when refering it to
> as well. But now I have a new problem which I can not find a way to solve.
> It may be my schema, but not sure. Here it is.
>
> If my XML is something like,
> <order>
> <salestax>
> <amount>1.23</amount>
> <rate>8.25</rate>
> </salestax>
> <itemlist>
> <item>
> <name>DVD</name>
> <price>12.00</price>
> <partnumber>12345</partnumber>
> </item>
> <item>
> <name>DVD</name>
> <price>12.00</price>
> <partnumber>12345</partnumber>
> </item>
> ... more <item> </item>
> </itemlist>
> </order>
> <order>
> ...
> ...
> </order>
>
> In my Schema, I have relation on <order .. sql:relation="xmlStaging">
> and all other has <element ... sql:is-constant="1">
> But when it comes to inserting Item in the table, it fails. The error I
> get is that the ItemName column was already found; make sure that no two
> columns has the same schema. What I need is when it finds a second Item,
> it inserts in a new row.
> Part of my Schema is below:
>
> <xsd:element name="type" type="xsd:string" sql:field="Type">
> </xsd:element>
> <xsd:element name="sku" type="xsd:string" sql:field="SKU">
> </xsd:element>
> <xsd:element name="partnumber" type="xsd:string" sql:field="Number">
> </xsd:element>
> <xsd:element name="description" type="xsd:string"
> sql:field="Description">
> </xsd:element>
> <xsd:element name="unitprice" type="xsd:string" sql:field="UnitPrice">
> </xsd:element>
> <xsd:element name="quantity" type="xsd:string" sql:field="Quantity">
> </xsd:element>
>
> <xsd:element name="item" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="type"/>
> <xsd:element ref="sku"/>
> <xsd:element ref="partnumber"/>
> <xsd:element ref="description"/>
> <xsd:element ref="unitprice"/>
> <xsd:element ref="quantity"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="lineitemlist" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence minOccurs="0" maxOccurs="unbounded">
> <xsd:element ref="item"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> .....
> .....
> .....
>
> <xsd:element name="order" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> <xsd:element ref="salestax" sql:is-constant="1"/>
> <xsd:element ref="shippingandhandling" sql:is-constant="1"/>
> <xsd:element ref="lineitemlist" sql:is-constant="1"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> Please help with the problem. Is there something in the schema I need to
> change? How can I make sure that anytime it finds a new <item>, it should
> go to a new row.
>
> Any help would be really appreciated,
> Thanks,
> Aleem
>
>
> On Wed, 13 Apr 2005, Aleem Rana wrote:
>
'batchheader'.]]>
element
elements if
rights.
Load.
sql:field="Merchant">
sql:field="OrderDate">
sql:field="PONumber">
sql:field="Address1">
sql:field="Address2">
have a
But
with
with
row.
be
>

Friday, March 23, 2012

Help! Problem with XSD mapping schema

Help! Problem with XSD mapping schema

Help! Problem with XSD mapping schema

Wednesday, March 21, 2012

HELP! I've lost a SSAS "cube" due to a "File ==> Save as XML"

Friday, February 24, 2012

Help with XQuery

Help with XQuery

Help with XQuery

Help with XML to Excel

Help with XML to Excel

Help with XML Bulk Load

Help with XML Bulk Load

Help with update forms

Blog Archive

About Me