problem with xsd and doulbe nested tags

I am having a problem with an xsd design (again). The client has decided to make a change and everything I try does not work. They have ADDED a nested tag to a nested tag - a person can have multiple addresses. There is a <PERSONID> tag that appears under
the <LIST> tag at the same level as the new tags. I need the ADDRESS info to end up in an ADDRESS table, with the PERSONID populated. The tags appear as:
<LIST><PERSONID>12345</PERSONID><ADDITIONAL ADDRESS><ADDRESS><ADDRESS_TYPE>Home</ADDRESS_TYPE><STREET>123 Main </STREET><CITY>Montreal</CITY></ADDRESS><ADDRESS><ADDRESS_TYPE>Business</ADDRESS_TYPE><STREET>695 Fuller Ave</STREET><CITY>Montreal</CITY></ADDR
ESS><ADDITIONAL ADDRESS></LIST>
Tables:
CREATE TABLE [dbo].[LIST] (
[personid] [int] NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ADDRESS] (
[personid] [int] NULL ,
[address_type] [char] (6) NULL,
[street] [char] (60) NULL ,
[city] [char] (50) NULL ,
) ON [PRIMARY]
GO
I tried the following schema, did not work. Any help would be greatly appreciated. Thanks.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation><xsd:appinfo><sql:relation ship name="LIST_ADDITIONAL_ADDRESS" parent="LIST" parent-key="personid" child="ADDITIONAL_ADDRESS" child-key="personid"/><sql:relationship name="LIST_ADDITIONA
L_ADDRESS_ADDRESS" parent="LIST_ADDITIONAL_ADDRESS" parent-key="personid" child="ADDITIONAL_ADDRESS_ADDRESS" child-key="personid"/></xsd:appinfo></xsd:annotation><xsd:element name="LIST" sql:relation="LIST"><xsd:complexType><xsd:sequence ><xsd:element name
="personid" type="xsd:string" minOccurs="0" sql:field="personid"/><xsd:element name="LIST_ADDITIONAL_ADDRESS" minOccurs="0" maxOccurs="unbounded" sql:relation="ADDITIONAL_ADDRESS" sql:relationship="LIST_ADDITIONAL_ADDRESS"><xsd:co mplextType><xsd:sequence>
<xsd:element name="ADDRESS" minOccurs="0" maxOccurs="unbounded" sql:relation="ADDRESS" sql:relationship="LIST_ADDITIONAL_ADDRESS_ADDRESS" ><xsd:complexType><xsd:sequence><xsd:element name="address_type" type="xsd:string" minOccurs="0" sql:field="address_ty
pe"/><xsd:element name="street" type="xsd:string" minOccurs="0" sql:field="street"/><xsd:element name="city" type="xsd:string" minOccurs="0" sql:field="city"/></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence><xsd:complextType><xsd:element></x
sd:sequence></xsd:complexType></element></xsd:schema>
Couple of things:
- I think you have an error at the end of your schema. You want to close
the complexType and element tags, not open new ones.
- I assume the "LIST_ADDITIONAL_ADDRESSES you refer to as the child in your
second relationship is the name of the first relatiionship? That doesn't
work. This can only refer to a table name.
- If all they did is add a wrapper element, can you use sql:is-constant?
--
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"darren" <anonymous@.discussions.microsoft.com> wrote in message
news:C5C0A64D-7EDD-4E9C-AECC-4A609EEAEA7B@.microsoft.com...
>I am having a problem with an xsd design (again). The client has decided to
>make a change and everything I try does not work. They have ADDED a nested
>tag to a nested tag - a person can have multiple addresses. There is a
><PERSONID> tag that appears under the <LIST> tag at the same level as the
>new tags. I need the ADDRESS info to end up in an ADDRESS table, with the
>PERSONID populated. The tags appear as:
> <LIST><PERSONID>12345</PERSONID><ADDITIONAL
> ADDRESS><ADDRESS><ADDRESS_TYPE>Home</ADDRESS_TYPE><STREET>123 Main
> </STREET><CITY>Montreal</CITY></ADDRESS><ADDRESS><ADDRESS_TYPE>Business</ADDRESS_TYPE><STREET>695
> Fuller Ave</STREET><CITY>Montreal</CITY></ADDRESS><ADDITIONAL
> ADDRESS></LIST>
> Tables:
> CREATE TABLE [dbo].[LIST] (
> [personid] [int] NULL ,
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ADDRESS] (
> [personid] [int] NULL ,
> [address_type] [char] (6) NULL,
> [street] [char] (60) NULL ,
> [city] [char] (50) NULL ,
> ) ON [PRIMARY]
> GO
> I tried the following schema, did not work. Any help would be greatly
> appreciated. Thanks.
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation><xsd:appinfo><sql:relation ship
> name="LIST_ADDITIONAL_ADDRESS" parent="LIST" parent-key="personid"
> child="ADDITIONAL_ADDRESS" child-key="personid"/><sql:relationship
> name="LIST_ADDITIONAL_ADDRESS_ADDRESS" parent="LIST_ADDITIONAL_ADDRESS"
> parent-key="personid" child="ADDITIONAL_ADDRESS_ADDRESS"
> child-key="personid"/></xsd:appinfo></xsd:annotation><xsd:element
> name="LIST"
> sql:relation="LIST"><xsd:complexType><xsd:sequence ><xsd:element
> name="personid" type="xsd:string" minOccurs="0"
> sql:field="personid"/><xsd:element name="LIST_ADDITIONAL_ADDRESS"
> minOccurs="0" maxOccurs="unbounded" sql:relation="ADDITIONAL_ADDRESS"
> sql:relationship="LIST_ADDITIONAL_ADDRESS"><xsd:co mplextType><xsd:sequence><xsd:element
> name="ADDRESS" minOccurs="0" maxOccurs="unbounded" sql:relation="ADDRESS"
> sql:relationship="LIST_ADDITIONAL_ADDRESS_ADDRESS" ><xsd:complexType><xsd:sequence><xsd:element
> name="address_type" type="xsd:string" minOccurs="0"
> sql:field="address_type"/><xsd:element name="street" type="xsd:string"
> minOccurs="0" sql:field="street"/><xsd:element name="city"
> type="xsd:string" minOccurs="0"
> sql:field="city"/></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence><xsd:complextType><xsd:element></xsd:sequence></xsd:complexType></element></xsd:schema>
|||that worked, thanks a lot for your help.
|||Glad to help.
Irwin
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"darren" <anonymous@.discussions.microsoft.com> wrote in message
news:62B6433B-C3CE-486F-B62A-C296D76BE433@.microsoft.com...
> that worked, thanks a lot for your help.