XML data from SQL table example

In this tutorial you will learn how to work with xml data in sql table, we can generate different form of xml data from sql table.

To understand how different type of XML format can be generated from SQL table, let’s create a SQL table and insert some test data to see the result.

Here i have created a table called "tbStock".

CREATE TABLE [dbo].[tbStock](
	[StockId] [bigint] IDENTITY(1,1) NOT NULL,
	[quantity] [int] NOT NULL,
	[price] [decimal](18, 2) NOT NULL,
	[productName] [varchar](50) NOT NULL,
	[updatedOn] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[StockId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Insert some sample data into sql table, so we can see the result after executing xml command in sql query analyzer.

    INSERT INTO tbStock
           ([quantity]
           ,[price]
           ,[productName]
           ,[updatedOn])
     VALUES
        (100, 325.05,'TCS', getdate(),
		1500, 55665.06,'ABS', getdate(),
		50, 708.05,'PPT', getdate(),
		120, 10.06,'LPB', getdate(),
		130, 8070.06,'GBH', getdate(),
		140, 325.05,'AMH', getdate(),
		160, 700.05,'VGT', getdate(),
		150, 403.05,'KOP', getdate(),
		400, 102.54,'PNBC', getdate())

Following example we learn "FOR XML AUTO" and "FOR XML PATH"

SELECT * FROM [dbo].[tbStock]
FOR XML AUTO

Xml auto will get each row as individual row, and all column as attribute value as displayed in following screenshot.

xml auto

SELECT * FROM [dbo].[tbStock]
FOR XML PATH

Xml path command will generate one single XML string where each row will be within row tag like below example.

xml auto

SELECT * FROM [dbo].[tbStock]
FOR XML PATH ('stock')

In Xml path command, we also can specify the business object name instead of default row tag, like in below example i have created “stock” element with all stock details inside.

xml auto

SELECT * FROM [dbo].[tbStock]
FOR XML PATH ('stock'),root('stocks')

In addition to what I have created in above code, I also can specify the root element of the xml, for example, we can have “stocks” as root element for all the stock.

xml auto

This may be very useful for transferring to a different system for patchwork, like sending quick update of data for any date range.