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
thanks for the Article… but i have a question..
i want the attribute value with the path specified.. for eg:..
sampleXML
colors id=’Complementary Colors’
color 1 color
color 2 color
colors
colors id = ‘Analogous Colors’
color 1 color
color 2 color
colors
colors id = ‘Neutral Colors’
color 1 color
color 2 color
colors
sampleXML
hello Pinal.
I have a problem with the ISO date format:
If I use the for xml with a date, you know, the result is an AUTO ISO conversion with a “T” as separator between date and time…
eg:
select GETDATE()
for xml path
2015-10-19T18:59:03.703
So, i need a date without this “T” separator, just like 2015-10-19 18:59:03.703
Do you have any idea?
Thanks in advance.
You can use CONVERT(CHAR(20),GETDATE(),120) function and choose format you need. I have a question to author. How to add namespace ONLY to header ??
WITH XMLNAMESPACES (‘url’ as tns)
SELECT ( SELECT ‘White’ AS [tns:Color1], …
This one adds to colors, fruits and SampleXML. I do need add it only to ROOT
Man thanks in advance
Hi can someone explain how it is possible to import an xml file into database from http source?
Hi Pinal, thank you, but your example is not creating an XML file, just create an XML Query output result. You need to use xp_cmdshell, and the bcp utility to store the query output to a XML file.
I can create the XML file and store in a physical location but when open it in a web browser, a blank screen is displayed, no data.
I notice , if open the XML file in Visual Studio, save, and then open it in a web browser, XML data is displayed correctly.
What i need to do? Serialize the XML?
Any suggestions?
Hi,
How can I add another level to your code between “SampleXML” and “Colors”?
Thanks a ton!
hi all, I am students, and I am working on my project. I have to create below requirements :
1. Prepare the specifications of semi structured extensions to your Database. Minimal Requirements: 3 extensions in data model and 5 operations on XMP data. Each extension of data model should be described with: name, affected relations, the method of storage of XML used i.e. CLOB, XMLType, CHAR. Each description of operations on semi structured data should be described with: name, corresponding SQL operation from workload, tools used for implementation i.e. SAX, DOM, XPATH, XQUERY.
2. Implementation of at least 3 semi structured extensions to data model.
3.Implementation of at least 5 operations on semi structured data. Document the execution times with semi structured extensions.
I created database and I use SQL SERVER 2012. So can any one tell me what should I do for these ?
Plz give me good example and explain me ..thank you .
You should post them on MSDN or StackOverFlow forums.
Using t-sql to generate an XML file is still one of the most annoying things I come across on a weekly basis. Just because you can, doesn’t mean you should.
Totally agree with you Jim.
Thanks for the Article…I want to write the query for below xml result, Is it possible ?
for eg:..sampleXML
White
Blue
Black
Green
Red
White1
Blue1
Black1
Green1
Red1
White11
Blue11
Black11
Green11
Red11
Okay, then what do you recommend to generate XML files?
I’m planing to use BCP to execute a stored proc.
I do not want to write code in C#.
Or use Powershell.
Or use SSIS.
Please?
(ooops, almost forgot the magic word!)