How to avoid creating a wrapping element when referencing an XML type in SQL?

Question

I'm fooling around with FOR XML in SQL Server 2008 to see if it would be a better fit for building web service response's rather than relying on Hibernate & HQL to map DTO's (or mapping them manually from flat result sets).

I created a fictive example where persons may have children and a collection of phone numbers.

I'm facing a situation where SELECT name FROM personCte will procude an unwanted wrapping <name> element, resulting in <name><name first="test" last="test"/></name>.

I can get rid of the extra wrapping element by doing the following, but I'm wondering if there's a more appropriate way of doing it?

SELECT (select name)
FROM personCte

One problem with that solution is that it cannot be used in CTE's, since all CTE columns must be named.

I would also like to know if there are better ways to unflatten multiple properties into a single element (e.g firstName and lastName into name) than doing a subquery?

Here's the sample code I'm using:

DECLARE @Person TABLE (
    id int NOT NULL PRIMARY KEY IDENTITY(1, 1),
    firstName nvarchar(50) NOT NULL,
    lastName nvarchar(50) NOT NULL,
    parentId int NULL
);

DECLARE @PersonPhoneNumber TABLE (
    personId int NOT NULL,
    number char(12) NOT NULL
);


INSERT INTO @Person (firstName, lastName, parentId)
VALUES 
    ('Person', 'A', NULL),
    ('Person', 'B', 1),
    ('Person', 'C', 2);

INSERT INTO @PersonPhoneNumber
VALUES
    (1, '888-888-8888'),
    (1, '999-999-9999'),
    (3, '333-333-3333');



;WITH personCte AS (
    SELECT 
        id,
        (
            SELECT firstName AS [@first], lastName AS [@last]
            FROM @Person
            WHERE id = person.id
            FOR XML PATH('name'), TYPE
        ) AS name,
        (
            SELECT number
            FROM @PersonPhoneNumber
            WHERE personId = person.id
            FOR XML PATH(''), TYPE
        ) AS phoneNumbers,
        parentId
    FROM @Person person
)
SELECT 
        id, 
        (SELECT name), /* Used to avoid unwanted wrapping name element */
        phoneNumbers, 
        parentId,
        (
            SELECT id, (SELECT name), phoneNumbers, parentId
            FROM personCte person
            WHERE parentId = p.id
            FOR XML AUTO, TYPE
        ) AS children
    FROM personCte p
FOR XML AUTO, ROOT('persons'), TYPE

Which correctly produces:

<persons>
  <person id="1">
    <name first="Person" last="A" />
    <phoneNumbers>
      <number>888-888-8888</number>
      <number>999-999-9999</number>
    </phoneNumbers>
    <children>
      <person id="2" parentId="1">
        <name first="Person" last="B" />
      </person>
    </children>
  </person>
  <person id="2" parentId="1">
    <name first="Person" last="B" />
    <children>
      <person id="3" parentId="2">
        <name first="Person" last="C" />
        <phoneNumbers>
          <number>333-333-3333</number>
        </phoneNumbers>
      </person>
    </children>
  </person>
  <person id="3" parentId="2">
    <name first="Person" last="C" />
    <phoneNumbers>
      <number>333-333-3333</number>
    </phoneNumbers>
  </person>
</persons>

Answer 1

You can use the query() XML method to exclude unwanted nesting:

select p.id, p.name.query('.')
FROM personCte p
FOR XML AUTO, ROOT('persons'), TYPE;

EDIT: What you need is to rewrite everything with PATH() syntax. In this case, you would not need any methods, and also you will be able to specify nested nodes, which doesn't work with AUTO. So, your full query will look like this:

;WITH personCte AS (
    SELECT id, parentId, firstName, lastName, (
        SELECT number
        FROM @PersonPhoneNumber
        WHERE personId = person.id
        FOR XML PATH(''), TYPE
    ) AS phoneNumbers
    FROM @Person person
)
SELECT 
    p.id as [@id],
    p.parentId as [@parentid],
    p.firstName AS [name/@first],
    p.lastName AS [name/@last],
    p.phoneNumbers,
    (
        SELECT id as [@id], parentId as [@parentid],
            person.firstName AS [name/@first],
            person.lastName AS [name/@last],
            phoneNumbers
        FROM personCte person
        WHERE parentId = p.id
        FOR XML path('person'), TYPE
    ) AS children
FROM personCte p
FOR XML path('person'), ROOT('persons'), TYPE;