BulkLoad and .xsd problem

I would like to load a xml file to a database. For reasons this I use the
BulkLoad
COM object model. The elements of the project are following:
The XML file:
<?xml version="1.0" encoding="ISO8859-2" ?>
<export>
<ceg id="0000000147">
<rovat id = "0">
<alrovat id = "1">
<mezo id = "bir">piros</mezo>
<mezo id = "cf">tarka</mezo>
</alrovat>
</rovat>
<rovat id = "2">
<alrovat id = "1">
<mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
</alrovat>
</rovat>
</ceg>
<ceg id="0000000153">
etc.
</ceg>
</export>
The columns of the tblExport table in the DB:
ceg_idchar(10)
rovat_idvarchar(10)
alrovat_idvarchar(10)
mezo_idvarchar(50)
mezo_textvarchar(1000)
The following xsd file has been used:
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ceg" sql:relation="[tblExport]">
<xsd:complexType>
<xsd:choice>
<xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="alrovat" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="mezo" sql:field="mezo_text"
minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id"
sql:field="mezo_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="alrovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="rovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
I've expected the following result set (and I would like to get same):
ceg_idrovat_idalrovat_idmezo_idmezo_text
000000014701birpiros
000000014701cftarka
000000014721nevFA-MAG Ipari Kisszovetkezet
0000000153etc.
But I've got this:
ceg_idrovat_idalrovat_idmezo_idmezo_text
0000000147NullNullNullNull
0000000153NullNullNullNull
etc.
Why? I tried to change the xsd file at many places and many times. For
example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
expected a reletionship on 'rovat'. But I don't want to take an extra table.
If I use sql:is-constant annotation an error will be raised saying that
constant element has no attribute. In most cases I don't get error but no
records will be generated.
Has anybody a good suggestion? I would be grateful for any help.
Thanks.
D. Attila
You have to use xsd:sequence instead of xsd:choice and specify
sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> I would like to load a xml file to a database. For reasons this I use the
> BulkLoad
> COM object model. The elements of the project are following:
> The XML file:
> --
> <?xml version="1.0" encoding="ISO8859-2" ?>
> <export>
> <ceg id="0000000147">
> <rovat id = "0">
> <alrovat id = "1">
> <mezo id = "bir">piros</mezo>
> <mezo id = "cf">tarka</mezo>
> </alrovat>
> </rovat>
> <rovat id = "2">
> <alrovat id = "1">
> <mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
> </alrovat>
> </rovat>
> </ceg>
> <ceg id="0000000153">
> etc.
> </ceg>
> </export>
> The columns of the tblExport table in the DB:
> ceg_id char(10)
> rovat_id varchar(10)
> alrovat_id varchar(10)
> mezo_id varchar(50)
> mezo_text varchar(1000)
> The following xsd file has been used:
> --
> <?xml version="1.0"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="ceg" sql:relation="[tblExport]">
> <xsd:complexType>
> <xsd:choice>
> <xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="alrovat" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="mezo"
sql:field="mezo_text"
> minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="id"
> sql:field="mezo_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="alrovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="rovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> <xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> I've expected the following result set (and I would like to get same):
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 0 1 bir piros
> 0000000147 0 1 cf tarka
> 0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
> 0000000153 etc.
> But I've got this:
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 Null Null Null Null
> 0000000153 Null Null Null Null
> etc.
> Why? I tried to change the xsd file at many places and many times. For
> example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
> expected a reletionship on 'rovat'. But I don't want to take an extra
table.
> If I use sql:is-constant annotation an error will be raised saying that
> constant element has no attribute. In most cases I don't get error but no
> records will be generated.
> Has anybody a good suggestion? I would be grateful for any help.
> Thanks.
> D. Attila
>
>
|||Thanks, but I have tried it. I get the following error:
......constant/fixed element cannot have attributes.....
But I need these attributes.
D.A.
"Bertan ARI [MSFT]" wrote:

> You have to use xsd:sequence instead of xsd:choice and specify
> sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> sql:field="mezo_text"
> table.
>
>
|||Sorry my mistake. I didn't see the attributes.
Unfortunately, your scenario is currently not supported by Bulkload.
Currently we do not allow attributes on constant elements and there are no
future plans to support it.
You may use XSLT to transform the Xml into a shape Bulkload can support or
you may use OpenXml which doesn't have this limitation.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...[vbcol=seagreen]
> Thanks, but I have tried it. I get the following error:
> .....constant/fixed element cannot have attributes.....
> But I need these attributes.
> D.A.
> "Bertan ARI [MSFT]" wrote:
rights.[vbcol=seagreen]
the[vbcol=seagreen]
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">[vbcol=seagreen]
maxOccurs="unbounded">[vbcol=seagreen]
sql:field="alrovat_id"[vbcol=seagreen]
/>[vbcol=seagreen]
> ----
> ----
that[vbcol=seagreen]
no[vbcol=seagreen]
|||Thanks for your response.
D.A.
"Bertan ARI [MSFT]" wrote:

> Sorry my mistake. I didn't see the attributes.
> Unfortunately, your scenario is currently not supported by Bulkload.
> Currently we do not allow attributes on constant elements and there are no
> future plans to support it.
> You may use XSLT to transform the Xml into a shape Bulkload can support or
> you may use OpenXml which doesn't have this limitation.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> rights.
> the
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> maxOccurs="unbounded">
> sql:field="alrovat_id"
> />
> that
> no
>
>