Question

我试图在2008年利用XML特征制作XML文件,但I m正在成为一个问题,即文件要求两个节点有相同的名称,但服务器自动将任何名称相同的节点聚集在一起。

例如:

<Location>
  <Address>Some Street</Address>
  <Address>Some other info</Address>
</Location>

问题在于我试图通过执行以下文件:

<Location>
  <Address>Some StreetSome other info</Address>
</Location>

这里,我如何选择数据:

 SELECT CustomerStreet1 AS  location/address ,
        CustomerStreet2 AS  location/address 
   FROM Customers
FOR XML PATH( Customer )

在两个地址领域之间选择另一个标的,但我期望我能够把两个地址的节点放在一起。

Answer 1

WITH    customers (id, CustomerStreet1, CustomerStreet2) AS
        (
        SELECT  1,  Some Street ,  Some other info 
        UNION ALL
        SELECT  2,  Second Street , NULL
        )
SELECT  id,
        (
        SELECT  *
        FROM    (
                SELECT  CustomerStreet1 AS Address
                UNION ALL
                SELECT  CustomerStreet2
                ) q
        FOR XML PATH(  ), TYPE
        )
FROM    customers 
FOR XML PATH( Customers )

友情链接