SQL XML help

Question

Post reply

SQL XML help

Guras

SSCertifiable

Points: 6518
More actions
September 7, 2022 at 5:22 pm
Go to Answer
#4085043
I have the following query that generates the output
SELECT
top 1 AccountNumber as 'ExternalId' from Account
FOR XML PATH('Element'),type
--output generated
<Element>
<ExternalId>3002543105</ExternalId>
</Element>

How can I get the following output
<Element ClassName= "class1">
<ExternalId TYPE="String">3002543105</ExternalId>
</Element>

Thank you in advance
- This topic was modified 2 years, 3 months ago by Guras.

drew.allen

SSC Guru

Points: 76997

September 7, 2022 at 7:44 pm

Answer

#4085107

SELECT 'class1' AS [@ClassName]
    , 'String' AS [ExternalId/@TYPE]
    , a.AccountNumber AS ExternalId
FROM #Account AS a
FOR XML PATH('Element')

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246990 More actions · Answer 1

Can you provide some sample data which we can test with?

This reply was modified 2 years, 3 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Guras SSCertifiable Points: 6518 More actions · Answer 2

The data is just simple I have a table named Account that stores the account number

Table : Account

3002543105

3002543106

Phil Parkin SSC Guru Points: 246990 More actions · Answer 3

Here is a dirty solution. Maybe there's a pretty solution out there that someone will come up with!

DROP TABLE IF EXISTS #Account;

CREATE TABLE #Account
(
    AccountNumber VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED
);

INSERT #Account
(
    AccountNumber
)
VALUES
('3002543105')
,('3002543106');

SELECT Res = CAST(REPLACE(
                             REPLACE(CAST(A.y AS VARCHAR(MAX)), '<Element>', '<Element ClassName="Class1">')
                            ,'<ExternalId>'
                            ,'<ExternalId TYPE="String">'
                         ) AS XML)
FROM
(
    SELECT ExternalId = a.AccountNumber
    FROM #Account a
    FOR XML PATH('Element'), TYPE
) A(y);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 4

There might be better ways, but I think Phil's solution looks good. Easy to enhance as well.

Guras SSCertifiable Points: 6518 More actions · Answer 5

I was playing around and I got the first part

SELECT

top 2 'class1' as [@ClassName]

,AccountNumber as 'ExternalId' from Account

FOR XML PATH('Element'),type

--generates output

</Element>

</Element>

But not sure how I add "TYPE" in <ExternalId> Tag.

Mark Cowne One Orange Chip Points: 26967 More actions · Answer 6

This, I think

SELECT 'Class1' AS "@ClassName",
       'String' AS "ExternalId/@TYPE",
   AccountNumber AS "ExternalId"
FROM #Account
FOR XML PATH('Element'),TYPE;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Guras SSCertifiable Points: 6518 More actions · Answer 7

Guras

SSCertifiable

Points: 6518

September 7, 2022 at 9:08 pm

#4085123

Thank you! That worked.