Search code examples
sqlsql-serverxmlt-sqlconcatenation

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


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?


Solution

  • --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');