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: