MS SQL 2005 "For XML Path" Node Layout Question

Question

I have the following query

Select field1 as 'node1/field1',
       field2 as 'node1/field2',
  (Select field3 as 'child1/field3',
          field4 as 'child1/field4'
   From table2
   FOR XML PATH(''),TYPE,Elements)
From Table1 FOR XML PATH('Root'),Elements

This produces:

<Root>
  <node1>
    <field1>data1</field1>
    <field2>data2</field2>
  </node1>
   <child1>
     <field3>data3</field3>
     <field4>data4</field4>
   </child1>
   <child1>
     ...   
</Root>

I would like the child1 nodes to be part of node1, not a separate node below.

<Root>
  <node1>
    <field1>data1</field1>
    <field2>data2</field2>
    <child1>
       <field3>data3</field3>
       <field4>data4</field4>
    </child1>
    <child1>
      ...
 </node1>
 <node1>
   ...
</Root>

I've tried putting node1 in the subquery PATH

FOR XML PATH('node1'),TYPE,Elements)

or prefixing the subquery field names with node1

Select field3 as 'node1/child1/field3',

but both create a new node1 element for the subquery.

Does anyone know how I can accomplish this?

Thanks

Answer 1

You've gotta tell SQL Server how table1 and table2 are related. Based on your answer below, I think something like this might do the trick:

select 
    table1.field1 as 'Node1/Field1'
,   table2.field1 as 'Node1/Child1/Field1'
,   table1.field2 as 'Node2/Field2'
from table1
left join table2 on table1.id = table2.table1id
for xml PATH(''), ROOT('Root')

This should produce XML like:

<Root>
    <Node1>
        <Field1>Value</Field1>
        <Child1>
            <Field1>Value</Field1>
        </Child1>
    </Node1>
    <Node2>
        <Field2>Value</Field2>
    </Node2>
</Root>

Answer 2

I've not done a lot of work with T-SQL and FOR XML, but i got round a similar problem by calling the FOR XML part of the query after each sub-query, as below, and using the PATH identifier to set the nodes:

SELECT field1 as "Field1",
    field2  as "Field2",
    (select
     field3 as "Field3",
     field4 as "Field4"

     from table2 t2 inner join 
     tlink tl on tl.id = t2.id inner join
     table2 on t2.id = tl.id
     group by field3, field4
     FOR XML PATH ('Child'), type
     ) 

from table2 t2 
group by field1, field2
FOR XML PATH('Node'), ROOT('Root')

this returns:

<Root>
  <Node1>
    <Field1>data1</Field1>
    <Field2>data2</Field2>
    <Child1>
      <Field3>data3</Field3>
      <Field4>data4</Field4>
    </Child1>
  </Node1>
  <Node2>
    <Field1>data1.2</Field1>
    <Field2>data2.2</Field2>
    <Child2>
      <Field3>data3.2</Field3>
      <Field4>data4.2</Field4>
    </Child2>

...
  </Node2>

...
</Root>

As Andomar mentioned, you need to make sure your data is joined correctly.

I've also got the Group By clause in to make sure data doesn't 'go astray'. I was having a problem with the sub-query data replicating as a child for each entry in the outer query (there were multiple children under each node related to how many nodes there were.) I'm sure there's a simple explanation but I was working to a tight schedule when I did this and never went back to check...

If this is incorrect usage or anyone can shed light on the repeating groups, please point it out and I'll edit...

Answer 3

My first sample query was not quite right, as you pointed out. Here is a more precise sample query.

Select field1 as 'node1/field1',       
       field2 as 'node1/field2',  
  (Select field3 as 'child1/field3',          
        field4 as 'child1/field4'   
   From table2   
   Where table1.ID = table2.ID
   FOR XML PATH(''),TYPE,Elements),
       field5 as 'node2/field5',
       field6 as 'node2/field6'
From table1 FOR XML PATH('Root'),Elements

Which produces:

<Root>
  <node1>
    <field1>data1</field1>
    <field2>data2</field2>
  </node1>   
  <child1>     
    <field3>data3</field3>
    <field4>data4</field4>
  </child1>
  <node2>
    <field5>data5</field5>
    <field6>data6</field6>
  </node2>
</Root>

field5 and field6 are fields from the outer query, but are within a different node path, node2. This is why I cannot have PATH('node') on the outer query. The outer query fields are used in many different node paths under Root. I need the subquery to retun under node1, and there are other subqueries that need to return under node2, node3...
I sure hope I am making sense. This is my first post and I will make sure and post a better sample query next time.

Thanks for the answers.

Dirk

Answer 4

It might make more sense if you post some sample data rather than using node1, field1, child1 etc. and explain which tables the data is coming from.

XML is hierarchical by nature. You can't arbitrarily start new nodes that don't relate to other nodes under the root, which is what it sounds like you're trying to do.

Everything within <node1>..to..</node1> relates to one record and the data generated by its sub-queries. The next node sequence will replicate the structure as <node2>..to..</node2> for the next record.

If you want more sub-queries under each node then just write each one in SQL with its own FOR XML PATH('SubNodeName')

Post your XSD or a sample of the XML code and I'll see if I can work out what you're trying to do.