SQL Server XML - using column name as the element name

Question

I'm writing a select statement for a large database whereby I'm copying from one system to another.

Both use the same column names as the element names.

For example

SELECT
    1 'job/queue',
    0 'job/branch',
    'create-apm' 'parameters/yzt/char20.1',
    c.Name 'apmdata/prospect/p.cm/',
    c.Addr1 'apmdata/prospect/p.cm/'
FROM
    dbo.ic_brpolicy AS p
INNER JOIN
    dbo.ic_yyclient AS c ON c.B@ = p.B@
                         AND c.Ref@ = p.Ref@
WHERE
    p.PolRef@ = 'ANCX38PC01'
FOR XML PATH('xmlexecute'), TYPE ;

You can see 'Name' and 'Addr1' both of these are the same names the elements need to be i.e.

c.Name 'apmdata/prospect/p.cm/Name'
c.Addr1 'apmdata/prospect/p.cm/Addr1'

Is there anyway I can get the column title as the output element without using dynamic SQL?

If dynamic SQL is the only way to do it then that's the route I'll have to go down as it'll save lots of time but still isn't ideal from a best practice point of view.

Cheers.

Lynchie · Accepted Answer · 2018-12-12 07:53:53Z

1

Managed to get around it by 'Sub Querying' the statement I needed.

SELECT
    1 'job/queue'
  , 0 'job/branch'
  , 'create-apm' 'parameters/yzt/char20.1'
  ,(
        SELECT REPLACE(c.[Addr1],'&','and') 'Address1',
               c.Addr2 'Address2',
               c.Addr3 'Address3',
               c.Addr4 'Address4',
               c.Pcode 'Postcode'
        WHERE c.B@ = p.B@
              AND c.Ref@ = SUBSTRING(p.PolRef@, 1, 6)
        FOR XML PATH('HomeAddress'), TYPE
    ) AS "apmdata/prospect/p.cm"
  --, c.Name 'apmdata/prospect/p.cm/Name'
  --, c.Addr1 'apmdata/prospect/p.cm/Addr1'
FROM
    dbo.ic_brpolicy AS p
INNER JOIN
    dbo.ic_yyclient AS c
        ON c.B@ = p.B@
           AND c.Ref@ = p.Ref@
WHERE
    p.PolRef@ = 'ANCX38PC01'
FOR XML PATH('xmlexecute'), TYPE ;

answered Dec 12, 2018 at 7:53

Lynchie

1,1492 gold badges22 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

SQL Server XML - using column name as the element name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related