I always want to learn SQL Server and XML file. Let us go over a 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 another article. XML data is known as self-describing or self-defining, meaning that the structure of the data is embedded with the data, thus when the data arrives there is no need to pre-build the structure to store the data; it is dynamically understood within the XML.
Reference: Pinal Dave (https://blog.sqlauthority.com)
125 Comments. Leave new
And i want to retrive data in xml format
Hi Pinal,
Can you help me, below xml string i need to read using sql query.
— SQL Server 2008 execution – xquery
DECLARE @xml xml = ‘
Host=ms0001xp/Pid=1400
mani
Thanigaimani
‘
SELECT V.col.query(‘UserId’).value(‘.’, ‘VARCHAR(32)’) AS UserID,
V.col.query(‘UserName’).value(‘.’, ‘VARCHAR(32)’) AS UserName
FROM @XML.nodes(‘StudyDeleteExtendedInfo’) AS V(col);
Note : Userid tag having xmlns namespace.
I have to read userid data from above query.
Regards
Mani
Hi Pinal,
I need to read the colors data from below xml using sql query.
Note : colors having xmlns namespace .
DECLARE @MyXML XML
SET @MyXML = ‘
White
Blue
Black
Green
Red
‘
SELECT
a.b.value(‘Colors[1]/Color1[1]’,’varchar(10)’) AS Color1
FROM @MyXML.nodes(‘SampleXML’) a(b)
Pls Help
Mani
How to produce the following XML?
White
Blue
Black
Green
Red
Apple
Pineapple
Grapes
Melon
@”
White
Blue
Black
Green
Red
Apple
Pineapple
Grapes
Melon
“
Excelent, thank you.
I was having problems extracting data from an SQL Database and writing into a formatted XML document, even after reading this. For anyone else with a similar question here was my query.
Select FirstName AS [First], LastName AS [Last]
From ResearchLibrary.dbo.Client
For XML PATH(‘Name’),
ROOT (‘Client’)
will produce this.
John
Doe
Jane
Doe
or rather…
Client
Name
First John /First
Last Doe/Last
/Name
Name
First Jane/First
Last Doe/Last
/Name
/Client
Forgot about the lt/gt tags :)
I want to Add the attribute inside the main nodes
my xml will be like
White
Blue
Black
Green
Red
Apple
Pineapple
Grapes
Melon
Please advice me …thanks in advance…
White
Blue
Black
Green
Red
Apple
Pineapple
Grapes
Melon
hi,
i have one table which contain id int,name varchar(256), and address xml.
suppose there is one record as following.
id = 1
name = Prem
address= jadibuti teku kalimati
i want to retrieve data in tabular format like
id name homeaddress officeaddress assistant address
1 prem jadibuti teku kalimati
how could this possible.
please help me.
Refer this post
This XML creation code is exactly what I needed! Thanks so much. I have found many good solutions on this website.
Hi,
I am facing issue in generating xml where I need to get value of the field of a table in a nodes.
Example please, I have a table name customer having field called owner_name. Field Name ‘owner_name’ in table customer has got 5 records. I need to display the xml in the following format
@owner_name1
@owner_name2
@owner_name3
@owner_name4
@owner_name5
Please let me know,how can I generate the xml like this.
Thanks
Hi Pinal,
Your articles are always simple and to the point to understand. Thanks.
I have a question. In the following code, I get xml fragment with value that include insignificant zeros at the end. How can I get the value without these zeroes? Thanks for your help.
declare @v float = ‘4.03’
select @v as Value from M_MaintenanceConfig for xml auto
I want to get:
document.getElementById(‘footer’).style.display = ‘none’;
FYI, the word “fruit” is already plural. There is no such thing as “fruits.”
Always good articals Pinal. Thanks.
Hi All,
I want to write the below code without using the Xml path, type or root. i.e. want to rewrite code using pure sql statements using xml functionalities. Will inserting just UNION ALL and removing XML path, type and root work for me.
Thanks in advance
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
HI Pinal,
I want to create a XML of this type.
Tags Email1,Email2 are dynamic. Also, NumberOfLeads will change for each listing.
Can you help with this one?
Thanks!
Mari
Pinal Bhai You are the Best.
Sat
Hi
how i can insert in the xml result on the top from a sql server 2008 query this ?
thanks