I always want to learn SQL Server and XML. Let us go over very simple example today about how to create XML using SQL Server.
Please also read related article here SQL SERVER – Simple Example of Reading XML File Using T-SQL.
Following is the XML which we want to create:
T-SQL Script to generate above XML:
SELECT ( SELECT 'White' AS Color1,
'Blue' AS Color2,
'Black' AS Color3,
'Light' AS 'Color4/@Special',
'Green' AS Color4,
'Red' AS Color5
FOR
XML PATH('Colors'),
TYPE
),
( SELECT 'Apple' AS Fruits1,
'Pineapple' AS Fruits2,
'Grapes' AS Fruits3,
'Melon' AS Fruits4
FOR
XML PATH('Fruits'),
TYPE
)
FOR XML PATH(''),
ROOT('SampleXML')
GO
Every XML has two elements.
1) Attributes and 2) Value.
In my above example color4 has attribute along with value. Make sure to specify attribute before the value is defined otherwise it will give error. We will talk about this in other article.