Creating a "specific" form of XML, without string concatenation in SQL server T-SQL

Question

I have some weird requirements that demand a specific XML format. Due to short deadlines and my lack of skills I decided to do a fast solution where I generate the XML through string concatenation.

DECLARE @tbl TABLE(personID int identity, name varchar(20), lastname varchar(20), country varchar(20));
INSERT INTO @tbl VALUES
 ('bob','bobby','USA')
,('mike','mikeson','Canada')
,('jack', 'jackson', 'Mexico')

select '<personID="' + cast(personID  as varchar) +'" country="' + country + '"/>' +
        '"<FIELD fieldname="name" value="' + name + '"/>' +
        '<FIELD fieldname="lastname" value="' + lastname + '"/>' +
'</personID>'
from @tbl

and this gives the output which I need. However, I've been told numerous times that this is not best practice, and creating XMLs through string concatenation is discouraged. Is there some other way I can achieve the same outcome using more advanced XML techniques?

The reason you're struggling and normal tools won't work is that you're not generating XML. You're generating something that resembles XML, but <personID="abc" confuses an element and an attribute. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 3, 2020 at 7:20

lptr · Accepted Answer · 2020-04-03 07:15:12Z

2

--string concat, is it valid xml??
select try_cast('<personID="' + cast(personID  as varchar) +'" country="' + country + '"/>' +
        '"<FIELD fieldname="name" value="' + name + '"/>' +
        '<FIELD fieldname="lastname" value="' + lastname + '"/>' +
'</personID>' as xml)
from @tbl;

--..add null columns
INSERT INTO @tbl VALUES
,(null, null, null)
,(null, 'null name', null)
,('null lastname', null, null);

--exclude fieldname for null columns
select
(
select t.personID as '@ID', t.country as '@country',
    case when t.name is not null then 'name' end as 'FIELD/@fieldname', 
    t.name as 'FIELD/@value',
    '',
    case when t.lastname is not null then 'lastname' end as 'FIELD/@fieldname', t.lastname as 'FIELD/@value'
for xml path('person'), type
)   
from @tbl t
where name is not null or lastname is not null or country is not null;

--A
select
(
select t.personID as '@ID', t.country as '@country',
    'name' as 'FIELD/@fieldname', t.name as 'FIELD/@value',
    '',
    'lastname' as 'FIELD/@fieldname', t.lastname as 'FIELD/@value'
for xml path('person'), type
)   
from @tbl t;

--B
select
(
select t.personID as 'person/@ID', t.country as 'person/@country',
    'name' as 'person/FIELD/@fieldname', t.name as 'person/FIELD/@value',
    '' as 'person',
    'lastname' as 'person/FIELD/@fieldname', t.lastname as 'person/FIELD/@value'
for xml path(''), type
)   
from @tbl t;


--single xml for the whole table
select t.personID as '@ID', t.country as '@country',
    'name' as 'FIELD/@fieldname', t.name as 'FIELD/@value',
    '',
    'lastname' as 'FIELD/@fieldname', t.lastname as 'FIELD/@value'
from @tbl t
for xml path('person');

edited Apr 3, 2020 at 7:15

answered Apr 3, 2020 at 6:50

lptr

6,8582 gold badges10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anton Over a year ago

Thank you very much for these examples. How would you extend one of these solutions so that incase the value is null it doesn't bring in any row?

lptr Over a year ago

you could filter the rows having all null columns with a where clause: ....from @tbl t where name is not null or lastname is not null or country is not null;

Collectives™ on Stack Overflow

Creating a "specific" form of XML, without string concatenation in SQL server T-SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related