FOR XML Clause

FOR XML CLAUSE:  Sometimes we need to data in form of XML from Database.SQL Server provide FOR XML Clause. For XML clause returns result of query as XML.There are some options with FOR XML clause

• Raw
• Path

·         Auto

• Elements(can be used with raw,auto,path)
• Explicit

You will find all differences from below query output

CREATE TABLE [dbo].[emp](

      [emp_id] [nchar](10) NULL,

      [emp_name] [nchar](10) NULL

) ON [PRIMARY]

insert into emp (emp_id,emp_name) values

('1','ABC'),

('2','XYZ'),

('3','PQR')

1. select *from dbo.emp for xml raw

<row emp_id="1         " emp_name="ABC       " />

<row emp_id="2         " emp_name="XYZ       " />

<row emp_id="3         " emp_name="PQR       " />

2. select *from dbo.emp for xml raw,elements

<row>

  <emp_id>1         </emp_id>

  <emp_name>ABC       </emp_name>

</row>

<row>

  <emp_id>2         </emp_id>

  <emp_name>XYZ       </emp_name>

</row>

<row>

  <emp_id>3         </emp_id>

  <emp_name>PQR       </emp_name>

</row>

3. select *from dbo.emp for xml auto

<emp emp_id="1         " emp_name="ABC       " />

<emp emp_id="2         " emp_name="XYZ       " />

<emp emp_id="3         " emp_name="PQR       " />

4. select *from dbo.emp for xml auto,elements

<emp>

  <emp_id>1         </emp_id>

  <emp_name>ABC       </emp_name>

</emp>

<emp>

  <emp_id>2         </emp_id>

  <emp_name>XYZ       </emp_name>

</emp>

<emp>

  <emp_id>3         </emp_id>

  <emp_name>PQR       </emp_name>

</emp>

5. select *from emp for xml auto

<emp emp_id="1         " emp_name="ABC       " />

<emp emp_id="2         " emp_name="XYZ       " />

<emp emp_id="3         " emp_name="PQR       " />

6. select *from emp for xml path('uma')

<uma>

  <emp_id>1         </emp_id>

  <emp_name>ABC       </emp_name>

</uma>

<uma>

  <emp_id>2         </emp_id>

  <emp_name>XYZ       </emp_name>

</uma>

<uma>

  <emp_id>3         </emp_id>

  <emp_name>PQR       </emp_name>

</uma>

Prev--->Basics of Index in SQL Server                                                 Next--->Handling Null values in SQL Server