For XML Explicit

I am trying to retrieve data from a single table as an xml document
and am having difficultes getting it to work...
The data is stored in a single table and each row can relate to
another row in the same table (see example below)...
ID ParentID Description
1 null Test one
2 1 Test two
3 1 Test three
4 2 Test four
Please can someone detail the SQL (FOR XML EXPLICIT) required to
retrieve this data in a hierarchical xml document.
Many Thanks in advance.
Hi,
if i have understand, you need to generate a hierarchical xml from
father->son relationship ?!
the left join help me to build a hierarchical xml with for xml auto, in this
case i now the max level number.
if you have the necessary time, try this approach.
i find a other exemple with for xml explicit and i send it to group
i hope to help you
Boss Hog
--Look, i have choose this methode in replacement of for XML explicit
select 1 as VISIBLE,
LEVEL0.CODE as ULID, LEVEL0.ISSHEET, LEVEL0.[DESCRIPTION] as LABEL,
LEVEL0.CCODE, LEVEL0.CCDESC, LEVEL0.HIERARCHIE, LEVEL0.NBCCODE,
LEVEL0.NBLABEL,
LEVEL1.CODE as ULID, LEVEL1.ISSHEET, LEVEL1.[DESCRIPTION] as LABEL,
LEVEL1.CCODE, LEVEL1.CCDESC, LEVEL1.HIERARCHIE, LEVEL1.NBCCODE,
LEVEL1.NBLABEL,
LEVEL2.CODE as ULID, LEVEL2.ISSHEET, LEVEL2.[CODEDESCRIPTION] as LABEL,
LEVEL2.CCODE, LEVEL2.CCDESC, LEVEL2.HIERARCHIE, LEVEL2.NBCCODE,
LEVEL2.NBLABEL,
LEVEL3.CODE as ULID, LEVEL3.ISSHEET, LEVEL3.[CODEDESCRIPTION] as LABEL,
LEVEL3.CCODE, LEVEL3.CCDESC, LEVEL3.HIERARCHIE, LEVEL3.NBCCODE,
LEVEL3.NBLABEL,
LEVEL4.CODE as ULID, LEVEL4.ISSHEET, LEVEL4.[CODEDESCRIPTION] as LABEL,
LEVEL4.CCODE, LEVEL4.CCDESC, LEVEL4.HIERARCHIE, LEVEL4.NBCCODE,
LEVEL4.NBLABEL,
LEVEL5.CODE as ULID, LEVEL5.ISSHEET, LEVEL5.[CODEDESCRIPTION] as LABEL,
LEVEL5.CCODE, LEVEL5.CCDESC, LEVEL5.HIERARCHIE, LEVEL5.NBCCODE,
LEVEL5.NBLABEL
from #TMPTBL LEVEL0
left join #TMPTBL LEVEL1
on LEVEL1.FATHER_ID=LEVEL0.HCODE
left join #TMPTBL LEVEL2
on LEVEL2.FATHER_ID=LEVEL1.HCODE
left join #TMPTBL LEVEL3
on LEVEL3.FATHER_ID=LEVEL2.HCODE
left join #TMPTBL LEVEL4
on LEVEL4.FATHER_ID=LEVEL3.HCODE
left join #TMPTBL LEVEL5
on LEVEL5.FATHER_ID=LEVEL4.HCODE
where LEVEL0.FATHER_ID is null
order by
LEVEL0.ISSHEET, LEVEL0.CODE,
LEVEL1.ISSHEET, LEVEL1.CODE,
LEVEL2.ISSHEET, LEVEL2.CODE,
LEVEL3.ISSHEET, LEVEL3.CODE,
LEVEL4.ISSHEET, LEVEL4.CODE,
LEVEL5.ISSHEET, LEVEL5.CODE
for xml auto
"Dan Coates" <danielcoates@.yahoo.com> wrote in message
news:53c81c7f.0408030812.5e4dac1b@.posting.google.c om...
> I am trying to retrieve data from a single table as an xml document
> and am having difficultes getting it to work...
> The data is stored in a single table and each row can relate to
> another row in the same table (see example below)...
> ID ParentID Description
> 1 null Test one
> 2 1 Test two
> 3 1 Test three
> 4 2 Test four
> Please can someone detail the SQL (FOR XML EXPLICIT) required to
> retrieve this data in a hierarchical xml document.
> Many Thanks in advance.
|||Dan,
FOR XML does not support building recursively nested XML of arbitrary depth
out of a self-referenced table.
Support for this feature is being evaluated for SQL 2005.
Your a few options.
If your hierarchy depth is known you can use:
- FOR XML EXPLICIT
- SQLXML mapping schema.
Otherwise your can extract your data as XML without recursive nesting and
reshape it with XSLT on the client side/middle-tier.
If you want to use FOR XML EXPLICIT you need:
1) Use T-SQL WHILE loop or a query with CTE in SQL 2005 to move your data
into a temp table or table variable adding integer column with level in the
hierarchy;
2) Do as many self-concatenations of the table (UNION ALL) with filtering by
appropriate level each time and using proper ordering.
Here's FOR XML EXPLICIT example for two level hierarchy (using SQL 2005
CommonTableExpression to get Level column, with SQL 2000 you can use WHILE
loop to build a temp table or a table variable):
WITH SelfRefTableWithLevel(ParentID, ID, Description, Level) AS
(
SELECT ParentID, ID, Description, 0 AS Level
FROM SelfRefTable
WHERE ParentID IS NULL
UNION ALL
SELECT t1.ParentID, t1.ID, t1.Description, Level + 1
FROM SelfRefTable t1
INNER JOIN SelfRefTableWithLevel t2
ON t1.ParentID = t2.ID
)
SELECT
1 Tag,
NULL Parent,
ParentID [emp!1!ParentID],
ID [emp!1!ID],
Description [emp!1!Description],
NULL [emp!2!ParentID],
NULL [emp!2!ID],
NULL [emp!2!Description]
FROM SelfRefTableWithLevel
WHERE Level = 0
UNION ALL
SELECT
2 Tag,
1 Parent,
NULL [emp!1!ParentID],
ParentID [emp!1!ID],
NULL [emp!1!Description],
ParentID [emp!2!ParentID],
ID [emp!2!ID],
Description [emp!2!Description]
FROM SelfRefTableWithLevel
WHERE Level = 1
ORDER BY [emp!1!ID], [emp!2!ID]
FOR XML EXPLICIT
GO
Hope it works for you.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"Dan Coates" <danielcoates@.yahoo.com> wrote in message
news:53c81c7f.0408030812.5e4dac1b@.posting.google.c om...
> I am trying to retrieve data from a single table as an xml document
> and am having difficultes getting it to work...
> The data is stored in a single table and each row can relate to
> another row in the same table (see example below)...
> ID ParentID Description
> 1 null Test one
> 2 1 Test two
> 3 1 Test three
> 4 2 Test four
> Please can someone detail the SQL (FOR XML EXPLICIT) required to
> retrieve this data in a hierarchical xml document.
> Many Thanks in advance.
|||I must mention that I oversimplified ordering problem with my example.
For proper ordering when your hierarchy is more than two level deep you
either need to do multiple self joins in SELECT for each level (number of
self joins would be the level each time) or construct an additional ordering
column.
Since the ordering column trick is more efficient and less obvious I will
give you a 3 level hierarchy example with a path ordering key.
WITH SelfRefTableWithLevelAndPath(ParentID, ID, Description, Level,Path) AS
(
SELECT ParentID, ID, 0 AS Level, convert(varchar(8000),ID) as Path
FROM SelfRefTable
WHERE ParentID IS NULL
UNION ALL
SELECT t1.ParentID, t1.ID, Level + 1,
Path+'/'+convert(varchar(8000),t1.ID)
FROM SelfRefTable t1
INNER JOIN SelfRefTableWithLevelAndPath t2
ON t1.ParentID = t2.ID
)
SELECT
1 Tag,
NULL Parent,
Path [emp!1!!hide],
ParentID [emp!1!ParentID],
ID [emp!1!ID],
Description [emp!1!Description],
NULL [emp!2!ParentID],
NULL [emp!2!ID],
NULL [emp!2!Description],
NULL [emp!3!ParentID],
NULL [emp!3!ID],
NULL [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 0
UNION ALL
SELECT
2 Tag,
1 Parent,
Path [emp!1!!hide],
NULL [emp!1!ParentID],
ParentID [emp!1!ID],
NULL [emp!1!Description],
ParentID [emp!2!ParentID],
ID [emp!2!ID],
Description [emp!2!Description],
NULL [emp!3!ParentID],
NULL [emp!3!ID],
NULL [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 1
UNION ALL
SELECT
3 Tag,
2 Parent,
Path [emp!1!!hide],
NULL [emp!1!ParentID],
NULL [emp!1!ID],
NULL [emp!1!Description],
NULL [emp!2!ParentID],
ParentID [emp!2!ID],
NULL [emp!2!Description],
ParentID [emp!3!ParentID],
ID [emp!3!ID],
Description [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 2
ORDER BY Path
for xml explicit
GO
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.