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?
Advertisement
Answer
--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');