Query problem ...

I'm creating a query that has a number of joins and I'm getting duplicate
values displayed nested as follows:
<Root>
<Paint StorePaintID="1">
<Brush StoreBrushID="10001" StoreBrokerageID="113"
receivedDate="2004-01-26T00:00:00" EstimatedValue="1.2">
<GlueLocation GlueLocationID="5" GlueLocation="New York, NY"
StoreType1="Housekeeping" StoreType2="Housekeeping"
StoreType3="Structured Product">
<CurrencyType CurrencyType="U.S. Dollar">
<Storywriter StorywriterPersonID="283">
<Artist ArtistPersonID="810">
<Tree TreeID="175">
<TreeType TreeDescription="RC" TreeCode="AB">
<ProductType ProductID="16" ProductDescription="Professional
Liability - Financial Institution D&O"
ProductCode="FDO">
<StoreUnit StoreUnit="1" LaHouseBrushStatus="Type A"
LaHouseBrushDate="2004-04-14T16:13:05.873"
LaHouseBrushStatusID="4795">
<Comments CommentText="sdagsadgsda"
commentDate="2004-01-26T00:00:00">
<House RBIStatusID="1926" statusDate="2004-02-19T00:00:00"
description="Red" />
<House RBIStatusID="1926" statusDate="2004-02-19T00:00:00"
description="Red" />
<House RBIStatusID="4795" statusDate="2004-04-14T16:13:05.873"
description="Green" />
<House RBIStatusID="4795" statusDate="2004-04-14T16:13:05.873"
description="Green" />
</Comments>
</StoreUnit>
</ProductType>
</TreeType>
</Tree>
</Artist>
</Storywriter>
</CurrencyType>
</GlueLocation>
</Brush>
</Paint>
<Paint StorePaintID="2">
<Brush StoreBrushID="10002" StoreBrokerageID="113"
receivedDate="2004-01-26T00:00:00" effectiveDate="2004-03-31T00:00:00"
EstimatedValue="3.1">
<GlueLocation GlueLocationID="5" GlueLocation="New York, NY"
StoreType1="Housekeeping" StoreType2="Structured Product"
StoreType3="Structured Product">
<CurrencyType CurrencyType="Yen">
<Storywriter StorywriterPersonID="746">
<Artist ArtistPersonID="810">
<Tree TreeID="176">
<TreeType TreeDescription="T1" TreeCode="CA">
<ProductType ProductID="9" ProductDescription="Professional
Liability - Commercial D&O" ProductCode="CDO">
<StoreUnit StoreUnit="1" LaHouseBrushStatus="Typce B"
LaHouseBrushDate="2004-03-24T08:45:56.107"
LaHouseBrushStatusID="3249">
<Comments CommentText="g" commentDate="2004-01-26T00:00:00">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asfsdaf"
commentDate="2004-03-08T13:54:25.200">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asdfasdg"
commentDate="2004-03-19T13:59:07.450">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="sdagasdg"
commentDate="2004-03-24T13:47:11.733">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asgasg"
commentDate="2004-03-26T13:16:44.797">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asdgasd"
commentDate="2004-03-26T14:52:12.607">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="gasdgasd"
commentDate="2004-03-29T12:18:06.480">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="gasdgasd"
commentDate="2004-03-31T10:15:17.513">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asdgsdag" commentDate="2004-01-26T00:00:00">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="sadgsadgsadgsad"
commentDate="2004-03-08T13:54:25.200">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="gasdg"
commentDate="2004-03-19T13:59:07.450">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="sdgasdg"
commentDate="2004-03-24T13:47:11.733">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asg" commentDate="2004-03-26T13:16:44.797">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="asdgasdgsadg"
commentDate="2004-03-26T14:52:12.607">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="sadgs"
commentDate="2004-03-29T12:18:06.480">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
<Comments CommentText="adgsadgsdag"
commentDate="2004-03-31T10:15:17.513">
<House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
description="Red" />
</Comments>
</StoreUnit>
</ProductType>
</TreeType>
</Tree>
</Artist>
</Storywriter>
</CurrencyType>
</GlueLocation>
</Brush>
</Paint>
</Root>
since I am using Biztalk I must use FOR XML AUTO for the query, could
someone please explain how I can get rid of the duplication of values?
The duplication on the leaf nodes indicate that you get two rows in your SQL
query.
Can you rewrite the SQL part of the query to only result in one row (e.g.,
using distinct)? Or would that remove duplicates that you want to have
exposed?
Thanks
Michael
"Keith Adler" <KeithAdler@.discussions.microsoft.com> wrote in message
news:9B608829-A601-44C8-B361-FA51530561BB@.microsoft.com...
> I'm creating a query that has a number of joins and I'm getting duplicate
> values displayed nested as follows:
> <Root>
> <Paint StorePaintID="1">
> <Brush StoreBrushID="10001" StoreBrokerageID="113"
> receivedDate="2004-01-26T00:00:00" EstimatedValue="1.2">
> <GlueLocation GlueLocationID="5" GlueLocation="New York, NY"
> StoreType1="Housekeeping" StoreType2="Housekeeping"
> StoreType3="Structured Product">
> <CurrencyType CurrencyType="U.S. Dollar">
> <Storywriter StorywriterPersonID="283">
> <Artist ArtistPersonID="810">
> <Tree TreeID="175">
> <TreeType TreeDescription="RC" TreeCode="AB">
> <ProductType ProductID="16" ProductDescription="Professional
> Liability - Financial Institution D&O"
> ProductCode="FDO">
> <StoreUnit StoreUnit="1" LaHouseBrushStatus="Type A"
> LaHouseBrushDate="2004-04-14T16:13:05.873"
> LaHouseBrushStatusID="4795">
> <Comments CommentText="sdagsadgsda"
> commentDate="2004-01-26T00:00:00">
> <House RBIStatusID="1926" statusDate="2004-02-19T00:00:00"
> description="Red" />
> <House RBIStatusID="1926" statusDate="2004-02-19T00:00:00"
> description="Red" />
> <House RBIStatusID="4795" statusDate="2004-04-14T16:13:05.873"
> description="Green" />
> <House RBIStatusID="4795" statusDate="2004-04-14T16:13:05.873"
> description="Green" />
> </Comments>
> </StoreUnit>
> </ProductType>
> </TreeType>
> </Tree>
> </Artist>
> </Storywriter>
> </CurrencyType>
> </GlueLocation>
> </Brush>
> </Paint>
> <Paint StorePaintID="2">
> <Brush StoreBrushID="10002" StoreBrokerageID="113"
> receivedDate="2004-01-26T00:00:00" effectiveDate="2004-03-31T00:00:00"
> EstimatedValue="3.1">
> <GlueLocation GlueLocationID="5" GlueLocation="New York, NY"
> StoreType1="Housekeeping" StoreType2="Structured Product"
> StoreType3="Structured Product">
> <CurrencyType CurrencyType="Yen">
> <Storywriter StorywriterPersonID="746">
> <Artist ArtistPersonID="810">
> <Tree TreeID="176">
> <TreeType TreeDescription="T1" TreeCode="CA">
> <ProductType ProductID="9" ProductDescription="Professional
> Liability - Commercial D&O" ProductCode="CDO">
> <StoreUnit StoreUnit="1" LaHouseBrushStatus="Typce B"
> LaHouseBrushDate="2004-03-24T08:45:56.107"
> LaHouseBrushStatusID="3249">
> <Comments CommentText="g" commentDate="2004-01-26T00:00:00">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asfsdaf"
> commentDate="2004-03-08T13:54:25.200">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asdfasdg"
> commentDate="2004-03-19T13:59:07.450">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="sdagasdg"
> commentDate="2004-03-24T13:47:11.733">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asgasg"
> commentDate="2004-03-26T13:16:44.797">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asdgasd"
> commentDate="2004-03-26T14:52:12.607">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="gasdgasd"
> commentDate="2004-03-29T12:18:06.480">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="gasdgasd"
> commentDate="2004-03-31T10:15:17.513">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asdgsdag" commentDate="2004-01-26T00:00:00">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="sadgsadgsadgsad"
> commentDate="2004-03-08T13:54:25.200">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="gasdg"
> commentDate="2004-03-19T13:59:07.450">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="sdgasdg"
> commentDate="2004-03-24T13:47:11.733">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asg" commentDate="2004-03-26T13:16:44.797">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="asdgasdgsadg"
> commentDate="2004-03-26T14:52:12.607">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="sadgs"
> commentDate="2004-03-29T12:18:06.480">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> <Comments CommentText="adgsadgsdag"
> commentDate="2004-03-31T10:15:17.513">
> <House RBIStatusID="12314" statusDate="2004-01-26T00:00:00"
> description="Red" />
> </Comments>
> </StoreUnit>
> </ProductType>
> </TreeType>
> </Tree>
> </Artist>
> </Storywriter>
> </CurrencyType>
> </GlueLocation>
> </Brush>
> </Paint>
> </Root>
> since I am using Biztalk I must use FOR XML AUTO for the query, could
> someone please explain how I can get rid of the duplication of values?
|||The problem is that i have many joins on this query, and each join results in
slightly more data being created. Some of the joined tables have n records.
It's a mess because I cannot figure out how to predict how the XML AUTO will
layout the elements. Unfortunately, because I'm using BizTalk 2004 I cannot
use XML EXPLICIT.
"Michael Rys [MSFT]" wrote:

> The duplication on the leaf nodes indicate that you get two rows in your SQL
> query.
> Can you rewrite the SQL part of the query to only result in one row (e.g.,
> using distinct)? Or would that remove duplicates that you want to have
> exposed?
> Thanks
> Michael
> "Keith Adler" <KeithAdler@.discussions.microsoft.com> wrote in message
> news:9B608829-A601-44C8-B361-FA51530561BB@.microsoft.com...
>
>
|||Auto mode works as follows:
It will generate a new element nesting for every source table/view, where
the nesting is determined left to right according to the order of the
columns in the select statement.
E.g.,
select T2.x, T1.x, T1.y, T2.x, T3.a
...
for xml auto
will result in
<T2 x=".." y="..">
<T1 x=".." y="..">
<T3 a=".."/>
</T1>
</T2>
In principle, every row will be mapped to a top-level element (starting at
T2), unless we can identify, that row number X is somehow related to row
number X-1. The way we do this is that we compare the values of the row with
the values of the previous row (except for image and (n)text columns).
So if you have
T2.x T1.x T1.y T2.x T3.a
1 3 4 2 5
1 3 4 3 5
1 3 5 3 6
1 3 5 3 6
We will first look at the values on the first row. Since it is the first
row. we will output:
<T2 x="1" y="2">
<T1 x="3" y="4">
<T3 a="5"/>
Then we look at the second row and see that T2.y has changed the value. Thus
the heuristic assumes a new T2 element and we close the old ones and open a
new one:
</T1>
<T2>
<T2 x="1" y="2">
<T1 x="3" y="4">
<T3 a="5"/>
The third row stays the same for the values of T2, but T1.y changes. So we
infer that we are still in the same T2 but have a new T1. So we produce:
</T1>
<T1 x="3" y="5">
<T3 a="6"/>
The fourth and last row is exactly the same as the previous one. However
since T3 is a leaf element, we will repeat it and then close the elements.
<T3 a="6"/>
</T1>
</T3>
In conclusion, this means that you want to always order by the key
properties of every level to get the right ordering and that you want to
eliminate duplicate rows for the leafs (T3.a in the example above).
HTH
Michael
"Keith Adler" <KeithAdler@.discussions.microsoft.com> wrote in message
news:491EE50B-21E0-4F98-A1DE-358D8441086A@.microsoft.com...[vbcol=seagreen]
> The problem is that i have many joins on this query, and each join results
> in
> slightly more data being created. Some of the joined tables have n
> records.
> It's a mess because I cannot figure out how to predict how the XML AUTO
> will
> layout the elements. Unfortunately, because I'm using BizTalk 2004 I
> cannot
> use XML EXPLICIT.
> "Michael Rys [MSFT]" wrote:

Query Plans of 2005 same in 2000 QA ?

I know there have been changes to certain execution plans in 2005.. For eg:
, there is no more bookmark loolup thats displayed in 2005, but is
represented as a nested loop join..
All i want to know is that if i run my query from SQL 2000 QA connected to
SQL 2005 instance, will it show the new plans if I ask for a display of plan
?
I dont have this setup now so cant test it myself, hence I thought I would
ask you..The information that the GUI query plans are using comes from the engine, so if you run QA against a
2005 server, you will see a nested loop join instead of a bookmark lookup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <Hassan@.hotmail.com> wrote in message news:e8IERBTKHHA.2236@.TK2MSFTNGP02.phx.gbl...
>I know there have been changes to certain execution plans in 2005.. For eg: , there is no more
>bookmark loolup thats displayed in 2005, but is represented as a nested loop join..
> All i want to know is that if i run my query from SQL 2000 QA connected to SQL 2005 instance, will
> it show the new plans if I ask for a display of plan ?
> I dont have this setup now so cant test it myself, hence I thought I would ask you..
>|||On Tue, 26 Dec 2006 12:56:28 -0800, Hassan wrote:
>I know there have been changes to certain execution plans in 2005.. For eg:
>, there is no more bookmark loolup thats displayed in 2005, but is
>represented as a nested loop join..
Hi Hassan,
And this will change yet again on SP2 (that is, the nested loop join
remains, but the second operator of the join will now labeled as a
clustered index lookup rather than a clustered index seek).
>All i want to know is that if i run my query from SQL 2000 QA connected to
>SQL 2005 instance, will it show the new plans if I ask for a display of plan
>?
I tested this with version 8.00.2039 (SP4) of QA and 9.00.2153 (SP1) of
SQL Server 2005, and the answer is: yes, it will display the new plans.
I'm not sure what happens if you try QA against an SP2 version of SQL
Server 2005, since QA has never heard of a "clustered index lookup"
operator. But I can't test that at the moment.
--
Hugo Kornelis, SQL Server MVP