Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Querying Microsoft SQL Server

I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.

My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.

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       " />


  1. 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>


  1. 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       " />


  1. 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>


  1. 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       " />


  1. 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>

Comments

Leave a comment on the original post [queryingsql.blogspot.com, opens in a new window]

Loading comments...