Add ElementNode attribute created using TSQL FOR XML Path

Question

I am trying to map some XML output in a SQLQL 2008 TSQL query. Corresponding XML code can return lists of elements. Lists are identified by attribute name, not node name.

The XML Path functions will return the node wrapper, but I cannot find a way to add any attributes to this node. In my case, they will be hard-coded values ​​based on the name of the table I'm running, they choose against.

I have an "x" as an "x" to keep the two lists separate. In my real data, this is not a problem, because they are in different nodes. The problem is how to get the attributes added to the "List" node.

Here is the SQL Fiddle page for an example below :

Circuit example

create table Table1 (Value varchar(50)); create table Table2 (Value varchar(50)); insert Table1 values ('A'), ('B'), ('C'); insert Table2 values ('X'), ('Y'), ('Z'); 

Sample selection

 select ( select Value as '@I' from Table1 for XML PATH('L'), TYPE ) as List, 'x' as 'x', -- needed to keep the Lists apart. ( select Value as '@I' from Table2 for XML PATH('L'), TYPE ) as List for XML PATH 

Actual output

 <row> <List> <LI="A"/> <LI="B"/> <LI="C"/> </List> <x>x</x> <List> <LI="X"/> <LI="Y"/> <LI="Z"/> </List> </row> 

Desired result: (adding the "Name" attribute to the list wrapper.)

 <row> <List Name='Table1'> <!-- Added Attribute "Name" here --> <LI="A"/> <LI="B"/> <LI="C"/> </List> <x>x</x> <List Name='Table2'> <!-- Added Attribute "Name" here --> <LI="X"/> <LI="Y"/> <LI="Z"/> </List> </row> 

Answer 1

This seems to give the right conclusion. To get an attribute on a List node, an additional XML PATH level is required:

 SELECT (SELECT 'Table1' AS '@name', (SELECT Value AS '@I' FROM Table1 FOR XML PATH ('L')) FOR XML PATH('List'), TYPE) ,'x' AS'x' ,( SELECT 'Table2' AS '@name', (SELECT Value AS '@I' FROM Table2 FOR XML PATH ('L')) FOR XML PATH('List'), TYPE ) FOR XML PATH;