SQL Server table data to xml file with column value

Question

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

I know SQL can give you XML like <data_t><code>subxxx</code><amount>123.00</amount><data_t/>.. but not like the result you expecting — Lee
– Lee, Commented Nov 23, 2017 at 9:02
Yep. I manage also produce XML file with root and tags - bur receiving side needs file in this format. :/ — Tsy79
– Tsy79, Commented Nov 23, 2017 at 9:05
I personally don't think you can get this with SQL, it seems more than normal XML. — Lee
– Lee, Commented Nov 23, 2017 at 9:19

Mikael Eriksson · Accepted Answer · 2017-11-23 09:25:55Z

3

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:

answered Nov 23, 2017 at 9:25

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

3 Comments

Tsy79 Over a year ago

Wow! Worked! Thousand times thank You! One option: If I have so called header information for the file - what would be the easiest way to add that into the result?

Mikael Eriksson Over a year ago

@Tsy79 if you are talking about the XML declaration have a look here. stackoverflow.com/questions/5423560/…

Tsy79 Over a year ago

Please find my next level question below.

Collectives™ on Stack Overflow

SQL Server table data to xml file with column value

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related