December 2, 2009 at 5:10 pm
I have parent-child hirearchy.
for example for the parent table has
parent PID (parent id) ,description
chaid table has CID ( child id) Parent ID ( foreign key) and description.
Let us say I have two child records for a given parent record.
I need my XML to look like as follows
<tree>
<parent>
<pid>1</pid>
<description> blah <description>
<child>
<cid>1<cid>
<description> blah <description>
</child>
<child>
<cid>2<cid>
<description> blah <description>
</child>
<parent>
</tree>
Now I know (kind of !!) how to get the above structure using the for XML:
Following works fine for me:
(SELECT pid
,description
,( SELECT cid
,description
FROM child c
INNER JOIN dbo.parent p
ON c.pid = p.pid
FOR XML PATH ('child'), TYPE))
FROM parent p1
WHERE p.pid = @mypid
FOR XML PATH('parent'),ELEMENTS, ROOT('tree')
------------
Now the problem is
when I do not have the PID or CID populated I still have the structure
<tree>
<parent>
<child/>
</parent>
</tree>
My requirement is:
1.If none of the parent/child values exist then show the XML as I need to have
<tree/> or NO XML at all
2 If there are no children for a given parent then show
<tree>
<parent>
<pid>1</pid>
<description> blah <description>
</parent>
</tree>
Any one know how this can be achieved? Appreciate your help.
TIA
December 3, 2009 at 9:02 am
Not sure if the above question was confusing. Please let me know if it needs further explanation.
December 3, 2009 at 3:23 pm
Please follow the first link in my signature on how to provide sample data.
I guess one of the reason that you don't have an answer yet are the missing table defs including sample data. In your case: DDL for dbo.child and dbo.parent together with some sample data and your expected result.
Some of us actually want to verify the solutions we're posting.
December 4, 2009 at 8:08 am
OK. I stand corrected.
I have created the test script based on my original script, simple but follows the same logic.
---------------------
Script to create tables
---------------------
USE [AdventureWorks]
GO
/****** Object: Table [dbo].[myparent] Script Date: 12/03/2009 15:10:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[myparent](
[parentid] [int] NOT NULL,
[pdesc] [nvarchar](10) NULL,
CONSTRAINT [PK_myparent] PRIMARY KEY CLUSTERED
(
[parentid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[mychild] Script Date: 12/03/2009 15:10:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[mychild](
[childid] [int] NOT NULL,
[cdesc] [nvarchar](10) NULL,
[parentid] [int] NULL,
CONSTRAINT [PK_mychild] PRIMARY KEY CLUSTERED
(
[childid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[mygrandchild] Script Date: 12/03/2009 15:10:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mygrandchild](
[grandchildid] [int] NOT NULL,
[gdesc] [varchar](10) NULL,
[childid] [int] NULL,
CONSTRAINT [PK_mygrandchild] PRIMARY KEY CLUSTERED
(
[grandchildid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: ForeignKey [FK_mychild_myparent] Script Date: 12/03/2009 15:10:53 ******/
ALTER TABLE [dbo].[mychild] WITH CHECK ADD CONSTRAINT [FK_mychild_myparent] FOREIGN KEY([parentid])
REFERENCES [dbo].[myparent] ([parentid])
GO
ALTER TABLE [dbo].[mychild] CHECK CONSTRAINT [FK_mychild_myparent]
GO
/****** Object: ForeignKey [FK_mygrandchild_mygrandchild] Script Date: 12/03/2009 15:10:53 ******/
ALTER TABLE [dbo].[mygrandchild] WITH CHECK ADD CONSTRAINT [FK_mygrandchild_mygrandchild] FOREIGN KEY([childid])
REFERENCES [dbo].[mychild] ([childid])
GO
ALTER TABLE [dbo].[mygrandchild] CHECK CONSTRAINT [FK_mygrandchild_mygrandchild]
GO
--------------------------------
Script to load tables
------------------------------------
USE [AdventureWorks];
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[myparent]([parentid], [pdesc])
SELECT 1, N'parent1' UNION ALL
SELECT 2, N'parent2' UNION ALL
SELECT 3, N'parent3'
COMMIT;
RAISERROR (N'[dbo].[myparent]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
USE [AdventureWorks];
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[mychild]([childid], [cdesc], [parentid])
SELECT 1, N'child1p1', 1 UNION ALL
SELECT 2, N'child2p1', 1 UNION ALL
SELECT 3, N'child3p2', 2
COMMIT;
RAISERROR (N'[dbo].[mychild]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
USE [AdventureWorks];
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[mygrandchild]([grandchildid], [gdesc], [childid])
SELECT 1, N'g1c1p1', 1 UNION ALL
SELECT 2, N'g2c1p1', 1 UNION ALL
SELECT 3, N'g3c2p1', 2 UNION ALL
SELECT 4, N'g4c2p1', 2
COMMIT;
RAISERROR (N'[dbo].[mygrandchild]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
--------------------------
Script to generate XML
--------------------
use adventureworks
SELECT parentid as 'pid'
,pdesc as 'desc'
,(SELECT childid as 'cid'
,cdesc as 'desc'
,(SELECT grandchildid as 'gid'
,gdesc as 'gdesc'
FROM mygrandchild g
INNER JOIN dbo.mychild c1
ON g.childid= c1.childid
Where c1.childid=c.childid
FOR XML PATH ('grandchild'), TYPE)
FROM mychild c
INNER JOIN dbo.myparent p
ON c.parentid = p.parentid
Where p.parentid=p1.parentid
FOR XML PATH ('child'), TYPE)
FROM myparent p1
Where p1.parentid=2
FOR XML PATH('parent'),ELEMENTS, ROOT('tree')
When I created the test tables and ran the query which I am working, its bit embarassing ( but fine) I am finding the right results. For example for record parent id 2 for which there are no Grand child records. It is returning same as ideally what I would have expected:
<tree>
<parent>
<pid>2</pid>
<desc>parent2</desc>
<child>
<cid>3</cid>
<desc>child3p2</desc>
</child>
</parent>
</tree>
My code is bit complicated, using the above logic the results I am getting are as follows
<tree>
<parent>
<pid>2</pid>
<desc>parent2</desc>
<child>
<cid>3</cid>
<desc>child3p2</desc>
<gid/>
</child>
</parent>
</tree>
See the extra <gid/> , in the above XML. Not sure why? I have checked the data, but seems to be fine. Any one observed such behavior?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply