CODEWITHSUNDEEP
sqlsql-serverxml
Tsy79
Tsy79

Reputation: 25

SQL Server table data to xml file with column value

I have this kind of data:

Data

Is it possible to produce this XML file as output with a SQL query from that table?

XML

Upvotes: 2

Views: 67

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Not sure what issues you actually had but you need to use for xml path() and nest them in a couple of sub-queries.

declare @T table(code varchar(30), amount money);

insert into @T(code, amount) values
('totalPAS', 389),
('sub270', 0),
('sub770', 0),
('sub270', 0);

select 'datfile' as '@objectCode', 
       (
       select T.code as '@instanceCode',
              'data_t' as '@objectCode',
              (
              select 'code' as 'ColumnValue/@name',
                     T.code as 'ColumnValue',
                     null,
                     'amount' as 'ColumnValue/@name',
                     T.amount as 'ColumnValue'
             for xml path('CustomInformation'), type
             ) 
       from @T as T
       for xml path('instance'), type
       )
for xml path('customObjectInstances');

Result:

Upvotes: 3

Related Questions