FOR XML PATH - How to obtain correct output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

FOR XML PATH - How to obtain correct output

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-12-21 : 06:21:00

Hi all

I am trying to use FOR XML PATH to extact xml in a specific format. One field in a child table contains an xml blob. I want to extract it without having to use a query method, which is a lot less performant.

However, I keep getting an additional xml node.

Does anyone know how to obtain the xml without including this additional node????

Below is a sample of what I want and what I am getting:

CREATE TABLE #Parent (ParentID int)
CREATE TABLE #Child (ChildID int, ParentId int, CatalogueItem xml)

INSERT INTO #Parent
SELECT 1

INSERT INTO #Child
SELECT 1,1,'<Child ChildID = "1" SomeStuff = "Stuff"/>'
UNION
SELECT 2,1,'<Child ChildID = "1" SomeStuff = "Stuff"/>'

-- Incorrect output
SELECT ParentID AS '@Parent',
	(
		SELECT CatalogueItem
		FROM #Child c
		INNER JOIN #Parent p ON p.ParentID = c.ParentID
		FOR XML PATH (''), TYPE
	)
FROM #Parent p
WHERE ParentID = 1
FOR XML PATH ('Parent')

/*
--RESULTS
Parent Parent="1">
  <CatalogueItem>
    <Child ChildID="1" SomeStuff="Stuff" />
  </CatalogueItem>
  <CatalogueItem>
    <Child ChildID="1" SomeStuff="Stuff" />
  </CatalogueItem>
</Parent>
*/

-- Required output
SELECT ParentID AS '@Parent',
	(
		SELECT CatalogueItem.query('Child')
		FROM #Child c
		INNER JOIN #Parent p ON p.ParentID = c.ParentID
		FOR XML PATH (''), TYPE
	)
FROM #Parent p
WHERE ParentID = 1
FOR XML PATH ('Parent')

/*
--RESULTS
<Parent Parent="1">
  <Child ChildID="1" SomeStuff="Stuff" />
  <Child ChildID="1" SomeStuff="Stuff" />
</Parent>
*/

DROP TABLE #Parent
DROP TABLE #Child

Thanks in advance

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources