create xml from sql query

Hi I have a "configuration" table in a SQL database it has the parentid and itemid to create a tree, I need to create another tree froma an xml but i dont know how to relate them in the xml file , because when i create the xmlfile all the items are alike, any hints?

Thanks and regards.

To get started you can look at the different FOR XML modes in SQL Server:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

FOR XML PATH is generally the one that give the most power with the least complexity:
http://msdn2.microsoft.com/en-us/library/ms189885.aspx

For more specific assistance, give us a little more context, about how your data is stored and how your rows are related to one another.

|||

The table has the tree structure, id, parent id, name where the parent id marks the realtion between the childs, when the parent id is null, it means that this is a parent node. I need to export it to an xml because one of my controls do not accept sql databinding only xml files.

Thanks and best regards.

|||

If you have a recursive structure like this, you can also investigate using CTE's or "Common Table Expressions" to generate recursive structures. Here are some examples:

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

You would want to write yours so that at each level, each nodes' child XML is nested within it.

|||

Great. I got resultset of parent child relationship using CTE . Now I want xml output as below

<Emp ManagerID="3" EmployeeID="9" ... />
<Emp ManagerID="3" EmployeeID="11" ... />
<Emp ManagerID="3" EmployeeID="158" ... >
<Emp ManagerID="158" EmployeeID="79" ... />
<Emp ManagerID="158" EmployeeID="114" ... />
<Emp ManagerID="158" EmployeeID="217" ... />
</Emp>
<Emp ManagerID="3" EmployeeID="263" ... >
<Emp ManagerID="263" EmployeeID="5" ... />
<Emp ManagerID="263" EmployeeID="265" ... />
</Emp>

Can you suggest idle way for same?

|||As posted earlier, now use the FOR XML to return the result formatted as XML. If you can, post your CTE and we can show you how to modify that to return the xml you desire.|||

Here is query

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level, Path)

AS

(

SELECT

ManagerID, EmployeeID, Title, DepartmentID, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path

FROM

HumanResources.Employee

WHERE

ManagerID IS NULL

UNION ALL

SELECT

e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))

FROM

HumanResources.Employee AS e

JOIN

DirectReports AS d

ON

e.ManagerID = d.EmployeeID

)

SELECT *

FROM DirectReports

ORDER BY Path

Hey, Thanks for your reply.

|||

Try something like this:

WITH DirectReports (ManagerID, EmployeeID, Title, Level, Path)

AS

(

SELECT ManagerID, EmployeeID, Title, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path

FROM HumanResources.Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, e.Title, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))

FROM HumanResources.Employee AS e

JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

SELECT *

FROM DirectReports

ORDER BY Path

FOR XML RAW, ROOT('Emp')

I think that is what you are looking for, but if not your best bet is to play with the FOR XML clause. I will also keep playing with it as well to make sure.

Scott