I have a table as an example:(where manager-id pointing back to the id field in the same table)

id|manager-id|name

1 |0 |John

2 |1 |Peter

Now, I am trying to work out a SQL statement, something like 'select.... for XML auto' to get the following XML as output.

<manager id="1" manger-id="0" name="John">
   <employee id="2" manager-id=1 name="Peter"/>
</manager> 

much appreciated!

CodePudding user response：

Easy enough to do for one level:

SELECT
    manager.id,
    manager.[manager-id],
    manager.name,
    (
        SELECT employee.id, employee.[manager-id], employee.name
        FROM employees As employee
        WHERE employee.[manager-id] = manager.id
        FOR XML AUTO, TYPE
    )
FROM
    employees As manager
WHERE
    manager.[manager-id] = 0
FOR XML AUTO, TYPE

Shape XML with Nested FOR XML Queries - SQL Server | Microsoft Docs

If you want deeper nesting, or more control over the output, you would probably be better to build the XML in the code which accesses the database, rather than trying to do it directly in SQL.

NB: Using 0 as the manager-id for top-level employees means you can't create a foreign key relationship, so there's nothing to prevent records from being orphaned. It would be better to use NULL instead, and have a proper FK in place.