CODEWITHSUNDEEP
sqlsql-servert-sql
John
John

Reputation: 1915

Change alias name based on condition in xml generated query

Please see the following sample code:

DECLARE @TEST TABLE (
  NAME VARCHAR(100)
)

INSERT into @TEST VALUES ('NAME_1')
INSERT into @TEST VALUES ('NAME_2')

SELECT NAME AS '@NAME'
  FROM @TEST
  FOR XML PATH ('POSITION'), ROOT ('POSITIONS')

The result is:

<POSITIONS>
  <POSITION NAME="NAME_1" />
  <POSITION NAME="NAME_2" />
</POSITIONS>  

I would like to condition the query to have something like this:

<POSITIONS>
  <POSITION NAME_MAIN="NAME_1" />
  <POSITION NAME_BACKUP="NAME_2" />
</POSITIONS>

Is it possible to condition the xml attribute based on THE queried value?

when NAME IS NAME_1 THEN NAME_MAIN
when NAME IS NAME_2 THEN NAME_BACKUP?

Upvotes: 0

Views: 251

Answers (1)

Devart
Devart

Reputation: 122032

Possible this be helpful for you -

DECLARE @TEST TABLE (NAME VARCHAR(100))

INSERT INTO @TEST 
VALUES ('NAME_1'), ('NAME_2')

SELECT 
      '@NAME_MAIN' = NULLIF(NAME, 'NAME_2')
    , '@NAME_BACKUP' = NULLIF(NAME, 'NAME_1') 
FROM @TEST
FOR XML PATH ('POSITION'), ROOT ('POSITIONS')

Output:

<POSITIONS>
  <POSITION NAME_MAIN="NAME_1" />
  <POSITION NAME_BACKUP="NAME_2" />
</POSITIONS>

Upvotes: 2

Related Questions