显式的SQL Server 2005 - 需要帮助格式 [英] SQL Server 2005 For XML Explicit - Need help formatting

问题描述

我有一个如下结构的表：

  ------------ ------------------ 
 LocationID | AccountNumber 
 ------------------------------ 
 long-guid-here | 12345 
 long-guid-here | 54321 
  

要传入另一个存储过程，我需要XML如下所示：

 < root> 
< clientID> 12345< / clientID> 
< clientID> 54321< / clientID> 
< / root> 
  

到目前为止我所能做的最好的事情就是像这样：

 < root clientID =10705/> 
  

我使用这个SQL语句：

 选择
 1作为标记，
作为父母，
 AccountNumber作为'root！1！clientID'
 FROM 
 Location.LocationMDAccount 
 WHERE 
 locationid ='long-guid-here'
 FOR XML EXPLICIT 
  

到目前为止，我已经看过 MSDN页面，但我还没有达到预期的结果。

---

@KG，
$ p


$ b $ < root>
< Location.LocationMDAccount>
< clientId> 10705< / clientId>
< /Location.LocationMDAccount>
< / root>


我将继续使用 FOR XML EXPLICIT

解决方案

尝试

选择
 1 AS标记，
 0作为父母，
 AccountNumber AS [Root！1！AccountNumber！元素] 
 FROM 
 Location.LocationMDAccount 
 WHERE 
 LocationID ='long-guid-here'
 FOR XML EXPLICIT 
  

I have a table with a structure like the following:

------------------------------
LocationID     | AccountNumber
------------------------------
long-guid-here | 12345
long-guid-here | 54321

To pass into another stored procedure, I need the XML to look like this:

<root> 
    <clientID>12345</clientID>
    <clientID>54321</clientID>
</root>

The best I've been able to do so far was getting it like this:

<root clientID="10705"/>

I'm using this SQL statement:

SELECT
    1 as tag,
    null as parent,
    AccountNumber as 'root!1!clientID'
FROM
    Location.LocationMDAccount
WHERE
    locationid = 'long-guid-here'
FOR XML EXPLICIT

So far, I've looked at the documentation on the MSDN page, but I've not come out with the desired results.

---

@KG,

Yours gave me this output actually:

<root>
  <Location.LocationMDAccount>
    <clientId>10705</clientId>
  </Location.LocationMDAccount>
</root>

I'm going to stick with the FOR XML EXPLICIT from Chris Leon for now.

解决方案

try

SELECT
    1 AS Tag,
    0 AS Parent,
    AccountNumber AS [Root!1!AccountNumber!element]
FROM
    Location.LocationMDAccount
WHERE
    LocationID = 'long-guid-here'
FOR XML EXPLICIT

这篇关于显式的SQL Server 2005 - 需要帮助格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！