MS SQL 2005“For XML Path”节点布局问题

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.

我希望child1节点成为node1的一部分,而不是下面的单独节点。

<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

我已经尝试将node1放在子查询PATH中

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

or prefixing the subquery field names with node1

或者使用node1为子查询字段名添加前缀

Select field3 as 'node1/child1/field3',

but both create a new node1 element for the subquery.

但是都为子查询创建了一个新的node1元素。

Does anyone know how I can accomplish this?

有谁知道我怎么能做到这一点?

Thanks

4 个解决方案

#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:

你必须告诉SQL Server table1和table2是如何相关的。根据你在下面的回答,我认为这样的事情可能会有所帮助:

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:

这应该产生如下XML:

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

#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:

我没有对T-SQL和FOR XML做过很多工作,但是我通过在每个子查询之后调用查询的FOR XML部分来解决类似的问题,如下所示,并使用PATH标识符来设置节点:

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.

正如Andomar所提到的,您需要确保正确连接数据。

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...

我还有Group By子句,以确保数据不会“误入歧途”。我对子查询数据的问题是外部查询中的每个条目复制为子节点(每个节点下有多个子节点与有多少节点相关。)我确定有一个简单的解释,但我是当我这样做并且从未回去检查时,工作紧张的时间表...

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

如果这是不正确的使用或任何人可以阐明重复组,请指出它,我将编辑...

#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.

field5和field6是外部查询的字段,但是在不同的节点路径node2中。这就是为什么我不能在外部查询上使用PATH('node')。外部查询字段在Root下的许多不同节点路径中使用。我需要子节点在node1下重新运行,还有其他子查询需要在node2,node3下返回...我当然希望我有意义。这是我的第一篇文章,我将确保并在下次发布更好的样本查询。

Thanks for the answers.

谢谢你的回答。

Dirk

#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.

如果您发布一些示例数据而不是使用node1,field1,child1等可能更有意义,并解释数据来自哪些表。

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.

XML本质上是分层的。您不能随意启动与根目录下的其他节点无关的新节点,这听起来就像您尝试做的那样。

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.

.. to .. 中的所有内容都与一条记录及其子查询生成的数据相关。下一个节点序列将结构复制为 ..到.. 以用于下一个记录。

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

如果您希望在每个节点下有更多的子查询,那么只需在SQL中使用自己的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.

发布你的XSD或XML代码的样本,我会看看我是否可以解决你想要做的事情。