Codeedoc - Programming Blog

SQL/XML In MS SQL Server

Microsoft SQL Server allows to return the results of a query in XML format.

Let’s create a simple table:

create table test
(
field_a varchar(20),
field_b varchar(20)
);

insert into test values(’Microsoft’,'SQL Server’);
insert into test values(’Oracle’,'Oracle Database’);

SQL to XML transformation is very simple to use. It’s done by adding following clause at the end of a query:

FOR XML mode [, XMLDATA] [, ELEMENTS] [, BINARY BASE 64]

The only required argument is mode. There three modes which can be used:

1. FOR XML RAW

Query:
select * from test for xml raw;

Result:

<row field_a=”Microsoft” field_b=”SQL Server”/>

<row field_a=”Oracle” field_b=”Oracle Database”/>

2. FOR XML AUTO

A)

Query:
select * from test for xml auto;

Result:

<test field_a=”Microsoft” field_b=”SQL Server”/>

<test field_a=”Oracle” field_b=”Oracle Database”/>

B)

Query:
select * from test for xml auto,elements;

Result:

<test>

 <field_a>Microsoft</field_a>

 <field_b>SQL Server</field_b>

</test>

<test>

 <field_a>Oracle</field_a>

 <field_b>Oracle Database</field_b>

</test>

3. FOR XML EXPLICIT

Query:
select 1 as tag, 0 as parent, field_a as [FieldA!1!Brand]
from test for xml explicit;

Result:

<FieldA Brand=”Microsoft”/>

<FieldA Brand=”Oracle”/>