Need help constructing query to return XML (FOR XML)

Question

Post reply

Need help constructing query to return XML (FOR XML)

Dan Williamson-368988

SSC Veteran

Points: 250
More actions
August 20, 2007 at 10:17 am

#373288

I have the following table:
CREATE TABLE [dbo].[MobileApplicationReport](
[MobileApplicationReportID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_MobileApplicationReport_MobileApplicationReportID] DEFAULT (newid()),
[MobileApplicationID] [uniqueidentifier] NOT NULL,
[CustomerID] [uniqueidentifier] NOT NULL,
[ParentID] [uniqueidentifier] NOT NULL,
[Title] [varchar](64) NOT NULL,
[Tooltip] [varchar](255) NOT NULL,
[Uri] [varchar](255) NOT NULL,
[Context] [bit] NOT NULL,
[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_MobileApplicationReport_IsDeleted] DEFAULT ((0)),
CONSTRAINT [PK_MobileApplicationReport] PRIMARY KEY NONCLUSTERED
(MobileApplicationReportID ASC)
Note the 'ParentID' column - this is a tree structure in this table. Please also note the UNIQUEIDENTIFIERS should make no difference to this problem - if it helps to make it clearer replace them with INT
I have the following query:
CREATE PROCEDURE MobileApplicationReportMenu
@MobileApplicationID UNIQUEIDENTIFIER,
@CustomerID
UNIQUEIDENTIFIER
AS
SELECT
'DefaultItemLook' "@LookId", MobileApplicationReportID "@Id", Title "@Text"
FROM
MobileApplicationReport WITH (NOLOCK)
WHERE
MobileApplicationID = @MobileApplicationID AND
CustomerID
= @CustomerID
FOR
XML PATH ('item'), ROOT('SiteMap')

which gives the following output:
<
SiteMap>
<
item LookId="DefaultItemLook" Id="8D613E36-ED81-402B-8A14-A49D57866401" Text="Bin Reports"/>
<item LookId="DefaultItemLook" Id="EE4BBCBC-44A5-4446-8183-A0F65FC1587C" Text="Bin Reports2"/>
</SiteMap>
However, I now have a request to return the full 'upwards' tree, i.e. this row and it's parent, and it's parent, etc.
The final output needs to look something like this (trivial example):
<SiteMap>
<item LookId="DefaultItemLook" Id="5b3d4e7d-759b-4599-b9eb-0acf62a78715" Text="Parent of Bin Reports">
<item LookId="DefaultItemLook" Id="8D613E36-ED81-402B-8A14-A49D57866401" Text="Bin Reports"/>
<item LookId="DefaultItemLook" Id="EE4BBCBC-44A5-4446-8183-A0F65FC1587C" Text="Bin Reports2"/>
</item>
</SiteMap>
The problem I am having is that I know how to do recursive joins in old style (2000), and have yet to use CTE for recursion. However looking closely it is not easy to see how to both recurse the tree AND to return it in the required XML format.
Any pointers or help at all most gratefully received.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Can you provide some sample data?

N 56°04'39.16"
E 12°55'05.25"

Dan Williamson-368988 SSC Veteran Points: 250 More actions · Answer 2

Sure, it's pretty trivial. Since it's so messy to write out i've only included 2 rows - a child and it's parent.

At the moment the "top" parent row has a '000....' UNIQUEID as it's ParentID, but that can be set to null if required (I guess it will be).

INSERT INTO [MobileApplicationReport]

([MobileApplicationReportID]

,[MobileApplicationID]

,[CustomerID]

,[ParentID]

,[Title]

,[Tooltip]

,[Uri]

,[Context]

,[IsDeleted])

VALUES

('ae4bbcbc-44a5-4446-8183-a0f65fc1587c', '5b1d4e7d-759b-4599-b9eb-0acf62a78715', '4092148d-beac-40ee-8057-90d542feb94d', '8d613e36-ed81-402b-8a14-a49d57866401', 'Bin report 1', False, False)

INSERT INTO [MobileApplicationReport]

([MobileApplicationReportID]

,[MobileApplicationID]

,[CustomerID]

,[ParentID]

,[Title]

,[Tooltip]

,[Uri]

,[Context]

,[IsDeleted])

VALUES

('8d613e36-ed81-402b-8a14-a49d57866401', '5b1d4e7d-759b-4599-b9eb-0acf62a78715', '4092148d-beac-40ee-8057-90d542feb94d', '00000000-0000-0000-0000-000000000000', 'Parent of Bin report 1', False, False)

Dan Williamson-368988 SSC Veteran Points: 250 More actions · Answer 3

So if you run all that code you can test with this:

MobileApplicationReportMenu '5b1d4e7d-759b-4599-b9eb-0acf62a78715', '4092148d-beac-40ee-8057-90d542feb94d'

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

First create this Stored Procedure

CREATE PROCEDURE dbo.uspMobileApplicationReportMenu

(

@MobileApplicationID UNIQUEIDENTIFIER,

@CustomerID UNIQUEIDENTIFIER

)

AS

SET NOCOUNT ON

;WITH peso ([@LookId], [@Id], [@Text])

AS

(

SELECT 'DefaultItemLook',

MobileApplicationReportID,

Title

FROM MobileApplicationReport WITH (NOLOCK)

WHERE MobileApplicationID = @MobileApplicationID

AND CustomerID = @CustomerID

AND ParentID = CAST(0x AS UNIQUEIDENTIFIER)

UNION ALL

SELECT 'DefaultItemLook',

i.MobileApplicationReportID,

i.Title

FROM MobileApplicationReport AS i WITH (NOLOCK)

INNER JOIN peso AS p ON p.[@Id] = i.ParentID

)

SELECT [@LookId],

[@Id],

[@Text]

FROM peso

FOR XML PATH ('item'),

ROOT('SiteMap')

Then call it with

EXEC uspMobileApplicationReportMenu '5b1d4e7d-759b-4599-b9eb-0acf62a78715', '4092148d-beac-40ee-8057-90d542feb94d'

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

The output (based on the sample data you have provided) look like this

</SiteMap>

N 56°04'39.16"
E 12°55'05.25"

Dan Williamson-368988 SSC Veteran Points: 250 More actions · Answer 6

Hi - many thanks for the reply.

This isn't quite what I was looking for however - I need the child row to be inside the parent row, e.g.:

</item> <-- This closes "Bin report 1"

</item> <-- This closes "Parent of Bin report 1"

</SiteMap>

Note that the only self-terminating node is the last child node, which itself has no children.

Thanks again, almost there now.

Dan Williamson-368988 SSC Veteran Points: 250 More actions · Answer 7

One small fix to the sp, as MobileApplicaitonID and CustomerID are required on the parents as well as the child:

CREATE PROCEDURE dbo.uspMobileApplicationReportMenu

(

@MobileApplicationID UNIQUEIDENTIFIER,

@CustomerID UNIQUEIDENTIFIER

)

AS

SET NOCOUNT ON

;WITH peso ([@LookId], [@Id], [@Text])

AS

(

SELECT 'DefaultItemLook',

MobileApplicationReportID,

Title

FROM MobileApplicationReport WITH (NOLOCK)

WHERE MobileApplicationID = @MobileApplicationID

AND CustomerID = @CustomerID

AND ParentID = CAST(0x AS UNIQUEIDENTIFIER)

UNION ALL

SELECT 'DefaultItemLook',

i.MobileApplicationReportID,

i.Title

FROM MobileApplicationReport AS i WITH (NOLOCK)

INNER JOIN peso AS p ON p.[@Id] = i.ParentID

WHERE MobileApplicationID = @MobileApplicationID

AND CustomerID = @CustomerID

)

SELECT [@LookId],

[@Id],

[@Text]

FROM peso

FOR XML PATH ('item'),

ROOT('SiteMap')

Then call it with

EXEC uspMobileApplicationReportMenu '5b1d4e7d-759b-4599-b9eb-0acf62a78715', '4092148d-beac-40ee-8057-90d542feb94d'

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

Why? I thought the MobileApplicationID was unique?

CREATE PROCEDURE dbo.uspMobileApplicationReportMenu

(

@MobileApplicationID UNIQUEIDENTIFIER,

@CustomerID UNIQUEIDENTIFIER

)

AS

SET NOCOUNT ON

;WITH peso ([@Id], [@Text], [Level])

AS

(

SELECT MobileApplicationReportID,

Title,

0

FROM MobileApplicationReport WITH (NOLOCK)

WHERE MobileApplicationID = @MobileApplicationID

AND CustomerID = @CustomerID

AND ParentID = CAST(0x AS UNIQUEIDENTIFIER)

UNION ALL

SELECT i.MobileApplicationReportID,

i.Title,

p.Level + 1

FROM MobileApplicationReport AS i WITH (NOLOCK)

INNER JOIN peso AS p ON p.[@Id] = i.ParentID

WHERE i.MobileApplicationID = @MobileApplicationID

AND i.CustomerID = @CustomerID

)

SELECT 'DefaultItemLook' AS [@LookId],

[@Id],

[@Text]

FROM peso

FOR XML PATH ('item'),

ROOT('SiteMap')

ORDER BY Level

N 56°04'39.16"
E 12°55'05.25"

Dan Williamson-368988 SSC Veteran Points: 250 More actions · Answer 9

The MobileApplicationID and CustomerID identify the collection of rows that make up a specific tree. The level of each row within a specific tree is determined by the ParentID of each row.

There are many seperate trees stored in this table, each tree having a unique MobileApplicationID and CustomerID.

I see why it is misleading - at first I also thought the parameters passed in (MobileApplicationID and CustomerID) were to identify the lowest child to return from the tree, but in fact we will only ever request the whole tree, which the two parameters are to identify.

I just tried your proc above (I had to move the ORDER BY above the FOR XML clause for it to compile), but I am getting the same output as before (note I added another row):

<

SiteMap>

<

item LookId="DefaultItemLook" Id="AAAAAABC-44A5-4446-8183-A0F65FC1587C" Text="All Reports" />

<

item LookId="DefaultItemLook" Id="8D613E36-ED81-402B-8A14-A49D57866401" Text="Bin Reports" />

<

item LookId="DefaultItemLook" Id="EE4BBCBC-44A5-4446-8183-A0F65FC1587C" Text="Missing Bins" />

</

SiteMap>

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

I haven't access to SQL Server 2005 right now, but what happens if you add

, ELEMENTS

at the end?

N 56°04'39.16"
E 12°55'05.25"

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

You can't have <Items/> be children of other <Items/> - that's no longer a "valid" xml document, which seems to be why it's collapsing them back to one level only.

you'll probably need to know the tree depth, OR write a dynamic SQL statement that gets finally executed (probably best implementing a XML EXPLICIT at that point).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?