CODEWITHSUNDEEP
sqlxmlt-sql
epi82
epi82

Reputation: 497

TSQL FOR XML EXPLICIT - add attribute to Element

I have a SQL query like this using FOR XML EXPLICIT:

declare @address table
(
    AddressID int,
    AddressType varchar(12),
    Address1 varchar(20),
    Address2 varchar(20),
    City varchar(25)
)    
insert into @address
select 1, 'Home', 'abc', 'xyz road', 'RJ' union all
select 2, 'Office', 'temp', 'ppp road', 'RJ' union all
select 3, 'Temp', 'eee', 'olkiu road', 'CL'

SELECT
    1 AS Tag,NULL AS Parent,
    0 AS 'AddressCollection!1!Sort!hide',
    NULL AS 'AddressCollection!1!',
    NULL AS 'Address!2!AddressID',
    NULL AS 'Address!2!AddressType!Element',
    NULL AS 'Address!2!Address1!Element',
    NULL AS 'Address!2!Address2!Element',
    NULL AS 'Address!2!City!Element'
UNION ALL
SELECT
    2 AS Tag,1 AS Parent,AddressID * 100,NULL,
    AddressID,AddressType, Address1, Address2, City
    FROM @address
ORDER BY [AddressCollection!1!Sort!hide]
FOR XML EXPLICIT

And the XML result is:

<AddressCollection>
  <Address AddressID="1">
    <AddressType>Home</AddressType>
    <Address1>abc</Address1>
    <Address2>xyz road</Address2>
    <City>RJ</City>
  </Address>
  <Address AddressID="2">
    <AddressType>Office</AddressType>
    <Address1>temp</Address1>
    <Address2>ppp road</Address2>
    <City>RJ</City>
  </Address>
  <Address AddressID="3">
    <AddressType>Temp</AddressType>
    <Address1>eee</Address1>
    <Address2>olkiu road</Address2>
    <City>CL</City>
  </Address>
</AddressCollection>

How can I add attributes to Element? I've correctly add them on parent tag but I don't know how can add them to element tag.

I need something like this:

<AddressCollection>
  <Address AddressID="1">
    <AddressType>Home</AddressType>
    <Address1>abc</Address1>
    <Address2>xyz road</Address2>
    <City status="modified">RJ</City>
  </Address>
  <Address AddressID="2">
    <AddressType status="modified">Office</AddressType>
    <Address1>temp</Address1>
    <Address2>ppp road</Address2>
    <City>RJ</City>
  </Address>
  <Address AddressID="3">
    <AddressType>Temp</AddressType>
    <Address1 status="modified">eee</Address1>
    <Address2>olkiu road</Address2>
    <City>CL</City>
  </Address>
</AddressCollection>

Upvotes: 1

Views: 2628

Answers (2)

user2099834
user2099834

Reputation: 33

Correction to last part of sollution:

select
AddressID as "MainInfo/@ID"
,   AddressType as "Address/@Type"
,   Address1 + ' ' + Address2 as "Address/*"
,   City as "City/*"
from @Address
for xml path('Address'), root('AddressCollection')

Upvotes: 0

djangojazz
djangojazz

Reputation: 13242

Simple ditch the explicit and go with the xml path(), root() model instead:

XML explicit is an utter and total pain IMHO. I would not use it unless there is some reason you are being forced to. You can accomplish more with far less learning XML Path or XML Auto.

declare @address table
(
    AddressID int,
    AddressType varchar(12),
    Address1 varchar(20),
    Address2 varchar(20),
    City varchar(25)
)    
insert into @address
select 1, 'Home', 'abc', 'xyz road', 'RJ' union all
select 2, 'Office', 'temp', 'ppp road', 'RJ' union all
select 3, 'Temp', 'eee', 'olkiu road', 'CL'

select *
from @address
for xml path('Address'), root('AddressCollection')

-- for even more control and fun:

select
    AddressID as "MainInfo/@ID"
,   AddressType as "MainInfo/@Type"
,   Address1 + ' ' + Address2 as "Address/*"
,   City as "Address/*"
from @Address
for xml path('Address'), root('AddressCollection')

Upvotes: 2

Related Questions