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
Hello Pinal ,
I am using BCP command to create xml file on remote server.
on remote server xml is created successfully, but subnodes are getting brakes. please provide me soluation to resolve subnode brake problam.
Thanks
Sanjay
Hi,
I need help on creating a stored procedure which can fetch the data from database and write the output to an xml file.
Kindly help. Thanks in advance!
Also, I need to mention the database used in SQL 2008.
Its really helpful. Thank you!!
Will using this method properly encode any XML special characters?
Try this:
SELECT(SELECT ‘White’ AS Color FOR XML PATH(”), TYPE),
(SELECT ‘Blue’ AS Color FOR XML PATH(”), TYPE),
(SELECT ‘Black’ AS Color FOR XML PATH(”), TYPE),
(SELECT ‘Light’ AS ‘Color/@special’, ‘Green’ AS Color FOR XML PATH(”), TYPE),
(SELECT ‘Sun’ AS ‘Color/@subtype’, ‘Red’ AS Color FOR XML PATH(”), TYPE)
FOR XML PATH(”), ROOT(‘Colors’)
GO
–Generated XML:
—
— White
— Blue
— Black
— Green
— Red
—
I think this gives a better example of depth.
SELECT ( SELECT ‘White’ AS Color1,
(select ‘dark’ as shade1,’Light’ as shade2 for XML path (‘Blue’),TYPE ),
‘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
hii,
i have one table where the datas are
id value
1 50
2 60
and another table having datas like
value val
50 20
50 30
60 100
60 120
But i want the result like
50 20
60 100
plz help me
select value,min(val) as val from table
group by value
hi
this helped me achieve the following out put
ABBEY WAY
WILLESBOROUGH
FRIDAY
ABBOTT WAY
TENTERDEN
TUESDAY
SQL QUERY used
select X.id as ‘@id’
, X.Street AS ‘street’
, X.district as ‘district’
, X.collectionday as ‘collectionday’
FROM [ABC-Collections].[dbo].[IMPORT_XML] X
for XML PATH(‘location’), root(‘addresses’)
Hope that helps someone
Hey friends, this query is really appreciative once and its working fine but for example if a table consists of 30,000 records then this ” select * from test1 for Xml AUTO,elements,root(‘Sample’) ” query converts them ? please immediately respond
Great – helped me a lot – thanks
Hi Pinal Dave,
I have tried XML PATH functionality for convert my query output to xml data convertion. its working fine. I can able to create a xml files for my data. But i need one favor from you. if i open the xml file in notepad or some other editers, the data is coming in paragraph format. i need correct xml format.
if i open the xml file its showing correct format.
example:
my data is
Name Age Sex
Mano 29 M
Raji 23 F
after i convert my data to xml, it is coming like this
Mano 29 M and so on..
but i need
Mano
29
M
and
so
on…
like this.
is it possible? if you give solution it will be a great help to me.
thanks in advance.
Mano
Hi, Dave.
I have a requirement such that there should be no self closing tags in an XML result I have to serve to another system.
If, for example, I do a query like this:…
SELECT ‘Fuit Shop’ AS ShopName
,” AS Nada
,(SELECT ‘Apple’ AS Fruits1
,’Pineapple’ AS Fruits2
,” AS Fruits3
,’Melon’ AS Fruits4
FOR XML PATH(‘Fruits’), TYPE)
FOR XML PATH(”), ROOT(‘SampleXML’)
… the result will be:
Fuit Shop
Apple
Pineapple
Melon
Note that while element Nada has a separate closing tag, Fuit3 is self-closing.
How could I modify that query to meet my requirement and get a result as follows?:
Fuit Shop
Apple
Pineapple
Melon
Thank you, very much.
Hi, Dave.
I’m rewriting my question to (hopefully) include proper XML tags
I have a requirement such that there should be no self closing tags in an XML result I have to serve to another system.
If, for example, I do a query like this:…
SELECT ‘Fuit Shop’ AS ShopName
,” AS Nada
,(SELECT ‘Apple’ AS Fruits1
,’Pineapple’ AS Fruits2
,” AS Fruits3
,’Melon’ AS Fruits4
FOR XML PATH(‘Fruits’), TYPE)
FOR XML PATH(”), ROOT(‘SampleXML’)
… the result will be:
<SampleXML>
<ShopName>Fuit Shop</ShopName>
<Nada></Nada>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3 />
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>
Note that while element Nada has a separate closing tag, Fuit3 is self-closing.
How could I modify that query to meet my requirement and get a result as follows?:
<SampleXML>
<ShopName>Fuit Shop</ShopName>
<Nada></Nada>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3></Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>
Thank you, very much.
Hi Pinal Sir,
Ur RND is nice, but i cant understand how to execute it after creating XML link which shown above at last image.. so please tell me what is next procedure after creating XML link.. :)
Thanks and regards
Pradeep K
Great post! Is there a way to mimic this output with SQL?
Object Name
Property
Length of Title
Right Padded with Spaces
Length With Spaces
Chart Color
I have only been able to get it to look like this:
Object Namenot NameObject NameObject Name
how to get XML like below:
urn:epc:id:sgtin:1249612083.107346.
urn:epc:id:sgtin:1249612083.107346.550006729 urn:epc:id:sgtin:1249612083.107346.550006730 urn:epc:id:sgtin:1249612083.107346.550006731 urn:epc:id:sgtin:1249612083.107346.550006732 urn:epc:id:sgtin:1249612083.107346.550006733 urn:epc:id:sgtin:1249612083.107346.550006734 urn:epc:id:sgtin:1249612083.107346.550006735 urn:epc:id:sgtin:1249612083.107346.550006736 urn:epc:id:sgtin:1249612083.107346.550006737 urn:epc:id:sgtin:1249612083.107346.550006738
GREAT THANKS!!!
“SQL SERVER – Simple Example of Creating XML File Using T-SQL | Journey to
SQL Authority with Pinal Dave” was indeed a wonderful post, can’t
help but wait to examine even more of your articles.
Time to waste a lot of time on the net lol. Thank you ,Amber
Hi Pinal, I have to create a xml file in the below mentioned format
2011-08-15 00:00:00
2011-10-19 07:53:25
1
15
70
1:R
322.0000
Can you please let me know on how to create this.