Convert columns to xml collection with attributes

Question

I would like to have a row for each row in the table, but transform the columns to an xml collection as efficiently as possible. In the example below it is a flattened table - but in the real world the columns would require many joins to get - resulting in many reads.

For example:

declare @tbl table (
 Id int identity (1, 1) primary key
,PolicyNumber   varchar(100) not null
,InsuredName    varchar(100) not null
,EffectiveDate  datetime2 not null
,Premium        numeric(22, 7)
)

insert into @tbl (PolicyNumber, InsuredName, EffectiveDate, Premium)
values ('2017A-ALKJ02', 'Insured Number 1', '2017-01-01', 1000)
  ,('2017A-BSDSDFWEF2', 'Insured Number 2', '2017-06-01', 2000)

select Id
  ,(select [@name] = 'PolicyNumber', [@type] = 'string', [text()] = PolicyNumber from @tbl [inner] where [inner].Id = [outer].Id for xml path ('dt'))
  ,(select [@name] = 'InsuredName', [@type] = 'string', [text()] = [inner].InsuredName from @tbl [inner] where [inner].Id = [outer].Id for xml path ('dt'))
  ,(select [@name] = 'EffectiveDate', [@type] = 'datetime', [text()] = [inner].EffectiveDate from @tbl [inner] where [inner].Id = [outer].Id for xml path ('dt'))
  ,(select [@name] = 'Premium', [@type] = 'numeric', [text()] = [inner].Premium from @tbl [inner] where [inner].Id = [outer].Id for xml path ('dt'))
from @tbl [outer]

Yields the individual columns in their own xml element, but I am after each row to have it's primary key and the structure:

<dts>
  <dt name="PolicyNumber" type="string">2017A-ALKJ02</dt>
  <dt name="InsuredName" type="string">Insured Number 1</dt>
  <dt name="EffectiveDate" type="datetime">2017-01-01T00:00:00</dt>
  <dt name="Premium" type="numeric">1000.0000000</dt>
</dts>

I understand this can be achieved with many sub-queries, but does anyone know of an easy way to have a single query that is smart enough to have the PK and all the individual columns transformed into an element in the dts collection?

Answer 1

Thanks for the suggestions! I decided flattening the query into a temp table is the best approach, then using the generic approach above plus the blank column trick satisfies the need.

if object_id('tempdb..#tmp') is not null
    drop table #tmp

create table #tmp (
     Id int identity (1, 1) primary key
    ,PolicyNumber   varchar(100) not null
    ,InsuredName    varchar(100) not null
    ,EffectiveDate  datetime2 not null
    ,Premium        numeric(22, 7)
);

insert into #tmp (PolicyNumber, InsuredName, EffectiveDate, Premium)
values ('2017A-ALKJ02', 'Insured Number 1', '2017-01-01', 1000)
  ,('2017A-BSDSDFWEF2', 'Insured Number 2', '2017-06-01', 2000);

DECLARE @cmd NVARCHAR(MAX)='
select [outer].Id
      ,convert(xml, (SELECT ' +
            STUFF(
            (
                SELECT ',[dt/@n] = ''' + c.name + '''' +
                      ',[dt/@t] = ''' + case when t.name = 'bit' then 'b'
                                             when t.name in ('date', 'smalldatetime', 'datetime2', 'datetime', 'datetimeoffset') then 'd'
                                             when t.name = 'bigint' then 'g'
                                             when t.name in ('tinyint', 'smallint', 'int', 'time', 'timestamp') then 'i'
                                             when t.name in ('real', 'smallmoney', 'money', 'float', 'decimal', 'numeric') then 'n'
                                             else 's'
                                        end + '''' +
                      ',[dt] = ' + QUOTENAME(c.name) +  
                      ',''''' 
                FROM tempdb.sys.columns c 
                  inner join sys.types t on c.system_type_id = t.system_type_id
                where object_id = object_id('tempdb..#tmp')
                FOR XML PATH('')
            ),1,1,'') + '
        FROM #tmp [inner]
        where [inner].Id = [outer].id
        for xml path (''dts'')))
from #tmp [outer]'

EXEC( @cmd);

Answer 2

If you know all metadata (column name and type) in advance this can be done very simply like here:

declare @tbl table (
 Id int identity (1, 1) primary key
,PolicyNumber   varchar(100) not null
,InsuredName    varchar(100) not null
,EffectiveDate  datetime2 not null
,Premium        numeric(22, 7)
);

insert into @tbl (PolicyNumber, InsuredName, EffectiveDate, Premium)
values ('2017A-ALKJ02', 'Insured Number 1', '2017-01-01', 1000)
  ,('2017A-BSDSDFWEF2', 'Insured Number 2', '2017-06-01', 2000);

SELECT 'PolicyNumber' AS [dt/@name]
      ,'string' AS [dt/@type]
      ,PolicyNumber AS [dt]
      ,''
      ,'InsuredName' AS [dt/@name]
      ,'string' AS [dt/@type]
      ,InsuredName AS [dt]
      ,''
      ,'EffectiveDate' AS [dt/@name]
      ,'datetime' AS [dt/@type]
      ,EffectiveDate AS [dt]
      ,''
      ,'Premium' AS [dt/@name]
      ,'numeric' AS [dt/@type]
      ,Premium AS [dt]
FROM @tbl 
FOR XML PATH('dts'),ROOT('root')

The result

<root>
  <dts>
    <dt name="PolicyNumber" type="string">2017A-ALKJ02</dt>
    <dt name="InsuredName" type="string">Insured Number 1</dt>
    <dt name="EffectiveDate" type="datetime">2017-01-01T00:00:00</dt>
    <dt name="Premium" type="numeric">1000.0000000</dt>
  </dts>
  <dts>
    <dt name="PolicyNumber" type="string">2017A-BSDSDFWEF2</dt>
    <dt name="InsuredName" type="string">Insured Number 2</dt>
    <dt name="EffectiveDate" type="datetime">2017-06-01T00:00:00</dt>
    <dt name="Premium" type="numeric">2000.0000000</dt>
  </dts>
</root>

The trick is the nameless empty "column" between the <dt> elements. The engine is told: Look, there's a new element, close the one before and start a new one!

Otherwise you'd get an error...

UPDATE: generic approach

This will extract all meta data and construct the same statement as above, which is executed with EXEC:

CREATE TABLE tmpTbl (
 Id int identity (1, 1) primary key
,PolicyNumber   varchar(100) not null
,InsuredName    varchar(100) not null
,EffectiveDate  datetime2 not null
,Premium        numeric(22, 7)
);

insert into tmpTbl (PolicyNumber, InsuredName, EffectiveDate, Premium)
values ('2017A-ALKJ02', 'Insured Number 1', '2017-01-01', 1000)
  ,('2017A-BSDSDFWEF2', 'Insured Number 2', '2017-06-01', 2000);

DECLARE @cmd NVARCHAR(MAX)='SELECT ' +
STUFF(
(
    SELECT ',''' + c.COLUMN_NAME + ''' AS [dt/@name]' +
          ',''' + c.DATA_TYPE + ''' AS [dt/@type]' +
          ',' + QUOTENAME(c.COLUMN_NAME) + ' AS [dt]' + 
          ',''''' 
    FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE TABLE_NAME='tmpTbl' 
    FOR XML PATH('')
),1,1,'') + 
'FROM tmpTbl FOR XML PATH(''dts''),ROOT(''root'')';
EXEC( @cmd);
GO
--cleanup (careful with real data)
--DROP TABLE tmpTbl;

If you need for example "string" instead of "varchar" you'd need a mapping table or a CASE expression.

Answer 3

This can be solve using different techniques. Here is one of them using UNPIVOT to generate the type column:

WITH DataSource AS
(
    SELECT [id]
          ,[column]
          ,[value]
          ,CASE [column]
                WHEN 'PolicyNumber' THEN 'string'
                WHEN 'InsuredName' THEN 'string'
                WHEN 'EffectiveDate' THEN 'datetime'
                WHEN 'Premium' THEN 'numeric'
           END AS [type]
    FROM 
    (
        SELECT Id
              ,PolicyNumber
              ,InsuredName
              ,CAST(EffectiveDate AS VARCHAR(100)) AS EffectiveDate 
              ,CAST(Premium AS VARCHAR(100)) AS Premium
        FROM @tbl
    ) DS
    UNPIVOT
    (
        [value] FOR [column] IN ([PolicyNumber], [InsuredName], [EffectiveDate], [Premium])
    ) UNPVT
)
SELECT DISTINCT [id]
               ,[Info]
FROM @tbl DS
CROSS APPLY
(
    SELECT [column]  "@name"
          ,[type] "@type"
          ,CASE WHEN [column] = 'EffectiveDate' THEN CONVERT(VARCHAR(32), CAST([value] AS DATETIME2), 126) ELSE [value] END "text()"
    FROM DataSource Info
    WHERE DS.[Id] = Info.[Id]
    FOR XML PATH('dt'), ROOT('dts')
) DSInfo (Info);

It will give you XML like this for each row:

<dts>
    <dt name="PolicyNumber" type="string">2017A-ALKJ02</dt>
    <dt name="InsuredName" type="string">Insured Number 1</dt>
    <dt name="EffectiveDate" type="datetime">2017-01-01T00:00:00</dt>
    <dt name="Premium" type="numeric">1000.0000000</dt>
</dts>

Answer 4

There is no convenient way to produce this kind of output in SQL Server. One possible solution might be a FLWOR transformation, but I suspect it will be quite convoluted, indeed.

The other is by using UNPIVOT, as in the example below, though it is far from being easily expandable:

select (
    select upt.ColumnName as [@name],
        isnull(dt.ColumnType, 'string') as [@type],
        upt.ColumnValue as [text()]
    from (
        select t.Id, t.PolicyNumber, t.InsuredName,
            convert(varchar(100), t.EffectiveDate, 126) as [EffectiveDate],
            cast(t.Premium as varchar(100)) as [Premium]
        from @tbl t
    ) sq
    unpivot (
        ColumnValue for ColumnName in (
            sq.PolicyNumber, sq.InsuredName, sq.EffectiveDate, sq.Premium
        )
    ) upt
        left join (values
            ('EffectiveDate', 'datetime'),
            ('Premium', 'numeric')
        ) dt (ColumnName, ColumnType) on upt.ColumnName = dt.ColumnName
    where upt.Id = t.Id
    for xml path('dt'), type
    )
from @tbl t
for xml path('dts'), type;

First, you need to transpose column values into rows, so that your relational output will start resembling your required XML. In order to fit all your columns into the same ColumnValue, you have to cast them to the same data type.

Second, you have to provide the data for your type attribute. In the example above, I used an inline table constructor, because there is no way you can get data types from TV columns on the fly. If your actual data resides in a static table, you can try to join it with system metadata objects, such as INFORMATION_SCHEMA.COLUMNS. Although for your required values you will probably need an additional mapping table, as well (in order to substitute varchar with string, for example).

Last, in order to get a single /dts element for every original table row, I join the unpivotted data with the table again. This allows to generate the required nesting of XML elements, because root() clause is unsuitable for this.