XML Output From SELECT Command in SQL Server 2005

XML is widely accepted platform-independent format for data presentation. Here, I am going to show some simple SQL ways to generate XML output from Database table or views in SQL Server 2005.

Let’s start with creating a Customer table and inserting those rows of data.


CREATE TABLE Customer
(
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
City nvarchar(50) NULL
)

INSERT INTO Customer(FirstName,LastName,City)
VALUES (‘A Rahim’,’Khan’,’Kurigram’)

INSERT INTO Customer(FirstName,City)
VALUES (‘A Karim’,’Dhaka’)


Syntax:

FOR XML {RAW | AUTO | EXPLICIT | PATH}
[, XMLDATA]
[, ELEMENTS]
[, BINARY BASE64]


SELECT * FROM Customer FOR XML RAW


SELECT * FROM Customer FOR XML AUTO


SELECT * FROM Customer FOR XML AUTO, ROOT(‘Customers’)


SELECT * FROM Customer WHERE FirstName = ‘A Karim’
FOR XML AUTO, ELEMENTS, ROOT(‘Customers’)

SELECT * FROM Customer WHERE FirstName = 'A Rahim'
FOR XML AUTO, ELEMENTS, ROOT(‘Customers’)


SELECT * FROM Customer WHERE FirstName = ‘A Karim’
FOR XML AUTO, ELEMENTS XSINIL, ROOT(‘Customers’)


SELECT FirstName as “Name/FirstName” ,
LastName as “Name/LastName”,
City as “Address/City”
FROM Customer WHERE FirstName = ‘A Karim’
FOR XML PATH(‘Customer’),ROOT(‘Customers’)


Thanks
A Rahim Khan

Advertisements
    • Kuldeep Bansal
    • December 24th, 2011

    Hi,

    I was reading your article and I would like to appreciate you for making it very simple and understandable. This article gives me a basic idea of SQL server database query and it will help me a lot. I have found another
    nice post over the internet related to this post which also explained very well. For more details of that post check this helpful link too…
    http://mindstick.com/Articles/35bd9656-f8a8-47c6-8bdb-67068727d529/?%E2%80%98Select%E2%80%99%20Command%20in%20SQL

    Thank you very much!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: