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.

Problem with WHERE on ROW_NUMBER when using nested query

Hi,

I am trying to limit a result set by ROW_NUMBER. However, I am having problems getting it working.

The following query works fine, and I get a result set with PollID, AddedDate and RowNum columns.

SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum FROM

( SELECT DISTINCT p.PollID, p.AddedDate

FROM vw_vs_PollsWithVoteCount p

JOIN vs_PollOptions o ON p.PollID = o.PollID

) AS Results

However, as soon as I add a WHERE condition:

SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum FROM

( SELECT DISTINCT p.PollID, p.AddedDate

FROM vw_vs_PollsWithVoteCount p

JOIN vs_PollOptions o ON p.PollID = o.PollID

) AS Results

WHERE RowNum BETWEEN 1 AND 10

The query fails with an ' Invalid column name 'RowNum' ' error.

I have tried using 'Results.RowNum' but I get the same problem.

I don't understand what the issue is. The result set has a column headed 'RowNum' so why can't I apply a WHERE clause to this column? I can apply WHERE to the PollID column, for example, with no problem.

Any help very much appreciated.

Thanks...

RowNum is just an alias for your row_number() which is not qualified as a filter column. You need to derive the select to use it.

e.g.

Code Snippet

select *

from (

SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum

FROM ( SELECT DISTINCT p.PollID, p.AddedDate

FROM vw_vs_PollsWithVoteCount p

JOIN vs_PollOptions o ON p.PollID = o.PollID

) AS Results

) derived

WHERE RowNum BETWEEN 1 AND 10

|||

Thanks for the reply. I tried doing this but I was worried that it would incur additional overhead and that there may be a more efficient way of doing it.

But thinking about it further, I can't see any reason why there should be a lot of extra overhead in doing this. My initial gut feeling was 'another SELECT statement, another nesting level, therefore more overhead'. But looking at what the outer SELECT statement is doing (i.e. very little) perhaps this isn't really an issue.

What do you think?

All that aside, it is great to have an explanation of why the query I suggested wasn't working. Thanks...

|||

You will be surprised to know that the cost (compilation) is so negligent that it does not even show up in the estimate - take a look at the execution plans for these. The cost is exactly 50/50. Sqlserver is pretty smart to _correctly_ flatten out the queries to produce the best plans (most of the times).

Code Snippet

select top 1 *
from sysobjects

select *
from(
select *
from(
select top 1 *
from sysobjects
)t1
)t2

|||

I've tried that and I see what you mean. Thanks very much for your excellent help with this.

|||

Just an addition, you can use CTE too.

Code Snippet

;with mycte
as (
SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum
FROM ( SELECT DISTINCT p.PollID, p.AddedDate
FROM vw_vs_PollsWithVoteCount p
JOIN vs_PollOptions o ON p.PollID = o.PollID
) Results
)
select *
from mycte
WHERE RowNum BETWEEN 1 AND 10