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
Gr8 post Ashish ..
Coverting result set to XML was of gr8 Use to me
Hi,
I am using XML Datatype for one table, this table i have inserted the XML Data. I want to know whare is the Data stored in the File Format…? Then i am using select query it’s take 15 seconds how to improve the performance for this table.
Can you give the solution…
Thanks,
Maniraj
Hi,
I am using XML Datatype for one table, this table i have inserted the XML Data. I want to know whare is the Data stored in the File Format…? Then i am using select query it’s take 15 seconds how to improve the performance for this table.
Can you give the solution…
Thanks,
Maniraj
Hello Pinal Dave
I do not know if this is the right place to post this question??
I am having a problem the next code is not working and I can not figure out why.
————————————
$sql->query(“SELECT col1, col2, col3 FROM table WHERE col4=’a_value’ ORDER BY id “);
$xml = ”;
$xml .= ”;
while ($row=$sql->fetch_row()) {
$col1 = $row[‘col1’];
$col2 = $row[‘col2’];
$col3 = $row[‘col3’];
$col1 = htmlspecialchars($col1);
$col2 = htmlspecialchars($col2);
$xml .= ”;
$xml .= ” . $col1 . ”;
$xml .= ” . $col2 . ”;
$xml .= ” . $col3 . ”;
$xml .= ”;
}
$xml .= ”;
echo $xml;
————————————–
Without the WHERE statement it works fine but with the WHERE statement in the query
I get the next error from the feed validator )
XML parsing error: :1:1: not well-formed (invalid token)
I am not well known to xml perhaps I find the answer on this page
With regards John J
Hello Pinal,
it is a great article but I have bit different requirement I need to generate xml in utf-8 format
there should a header like this

Windows Forms
728372837
How can I create such kind of xml
Thank YOu
Hi Pinal,
How to create phyical XML file from TSQL?
I had created one SP for generating XML, but not able to create physical XML file from it.
How can I create physcal XML file from SP?
Regards,
Yamraj Pandya
Hello,
could you please tell me how can I add arguments to the Root? I need it to be something like:
thanks!
hi,
i have a bulk xml data, i want to this data import in sql server 2005 using procedure.
pls tell me how to create procedure for this solution.
i am already create table and have a database in xml format
pls tell how can i create a procedure .
thnks
Hi Vinoth,
You can use SSIS to get these xml files into SQL SERVER 2005.
For reference:
You can implement this and configure data flow process, to transfer data from xml to SQL SERVER.
Let me know if I can help you to design SSIS package.
Thanks,
Tejas
SQLYoga.com
Hi tejas,
i am not asking about ssis package,
i want know about procedure to import xml data in sql server
pls help out this problem
thnks
Hai, i have 6 tables in my database and i want generate the xml file with 7 main root elements.
plz let me know.
Hi,
I want to write the xml output in “C:\Output.xml” file. How can i write my sql query. Is it possible in sql?
Regards,
V.M. Damodharan
Hello everyone!
I’m really desperate in getting my project done. :(
Can anyone help me here?
My problem is that I have to display the results of a “SELECT top 5 * FROM mytable order by mydate desc for xml auto” to an ASP.
Something like this printed out on a webpage:
Hello Pinal.
I’m trying to execute a query from an asp.net & C# application, using sql server as data base. I need to store the query result in a xml file.
could you help me please?
Than kyou and Regards!
This is really bad comment.
checking the comment functionality
hey,
take the result in a variable as an string and write that string to a file wherever your want, I don’t think there is any issue because you have the result in you hand
OR
you don’t know how to save the file from ASP.Net, then please search google for your help because it is a SQL related site and not the ASP.Net.
Pinal don’t know anything about the ASP.Net
Poor Guy
hi pinal,
i am using XML as datatype in my function but that function is taking too much time to return value..
is their any alternate way of XML datatype or can you help me to optimize my function performance and stored procedure performance..
many thanks in advance…
Your blog is great! It’s helped me out so many times!
Thanks!
How to export #table content to csv file.
Thanks
How to export #table content to csv file.
Great sample. I wasn’t getting it at first until I realized you have to click on the XML in the Results grid. Slick trick!
Is there any way to convert sql server database to XML in Query?
i want to retrive data from sql 2005 into xml file without inserting in xml formate
create table xml
(
id int identity(1,1) constraint pkid primary key,
name nvarchar(50),
img nvarchar(50),
content nvarchar(max),
won nvarchar(10)
)
how can we do that…?
pls help
thankx