Find a solution

SQL Server 2005 XML Explicit - Segítségre van szüksége a formázáshoz

original title: "SQL Server 2005 For XML Explicit - Need help formatting"

Translate

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.

Van egy asztalom, amelynek szerkezete a következő: ------------------------------ LocationID | Számlaszám ------------------------------ hosszú útmutató | 12345 hosszú idegenvezető | 54321 To ...

Ez az összefoglalás a fordítás után. Ha meg szeretné tekinteni a teljes fordítást, kattintson a "fordítás" ikonra

Minden válasz
  • Chris Leon
    Translate

    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
  • karlgrz
    Translate

    Try this, Chris:

    SELECT
    AccountNumber as [clientId]
FROM
    Location.Location root
WHERE
    LocationId = 'long-guid-here'
FOR
    XML AUTO, ELEMENTS

    TERRIBLY SORRY! I mixed up what you were asking for. I prefer the XML AUTO just for ease of maintainance, but I believe either one is effective. My apologies for the oversight ;-)

  • jeffreypriebe
    Translate

    Using SQL Server 2005 (or presumably 2008) I find for XML PATH to allow for much easier to maintain SQL than for XML Explicit (particularly once the SQL is longer).

    In this case:

    SELECT AccountNumber as "clientID"
FROM Location.LocationMDAccount
WHERE locationid = 'long-guid-here'
FOR XML PATH (''), Root ('root');
  • Chris Benard
    Translate

    I got it with:

    select
1 as tag,
null as parent,
AccountNumber as 'root!1!clientID!element'
from
Location.LocationMDAccount
where
locationid = 'long-guid-here'
for xml explicit
  • Asher
    Translate 
    SELECT 1             as tag,
       null          as parent,
       AccountNumber as 'clientID!1!!element'
FROM Location.LocationMDAccount
WHERE locationid = 'long-guid-here'
FOR XML EXPLICIT, root('root')

Hasonló problémák

Legutóbbi kérdések