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
”
2011-08-15 00:00:00
2011-10-19 07:53:25
1
15
70
1:R
322.0000
“
Hi.. I have a query .. Is it possible to change the xml element name ..?
For eg: i have an xml like 1result ; i need to change this xml values as 1qwe;
Hi.. I have a query .. Is it possible to change the xml element name ..? For eg: i have an xml like
1result ;
i need to change this xml values as
1qwe;
Hi ..
I have a query. Is it possible to change the xml elementname based on node value?
For eg:
abcd
67
efgh
69
to,
abcd
67
efgh
69
Hello Sir,
I have an XSD(schema defination) and a DB Table . So using both table and XSD is it possible to create resule XML. Please do let me know.
hello i have an error while executing this code
select * from authors for xml auto,elements(‘authors’),root(‘table’);
the error is
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘authors’.
please correct this and do me the favour
#Prabha
do you want the root element as “Table” and child element as authors
#Prabha please modify your query as like this one
select * FROM Product for XML AUTO, ROOT(‘Table’), ELEMENTS
Hi,
I have a question. How would you do the next exercises?:
hi, i just need multiple path and my data should obtain by xml RAW..could u help me plz
SCRIPT DOES NOT WORK REGARDLESS WHAT YOUR FRIENDS ARE SAYING
I have created an XML file from the output of sqlcmd however, the XML file created can’t be opened with Internet Exprorer. Why?. How can we create XML File from SQL that can be opened with Internet explorer. Couple of tags are missing like xml version, encoding, Document type etc.. How can we add them while generating the XML File?.
Rajan.S
When trying to export the XML created, we get a ‘The operation could not be completed. Not enough storage is available to complete this operation.” error. Is there a way to export my xml query directly to an file? How can I export and avoid this error?
Hello,
what is the concept of XML warehouse?
can i implement this using sql server?
What are the steps to build xml warehouse?
Thanks, this was a huge help.
Your welcome ChadT!
Thanks you
Thanks Indika.
HI pinal,
I want to know to write a SP using XML using more table data
simbu – can you please give more details?
How to generate below format using Sql Sever select query.
Happy Lines
OTHER
cpp permit: 0123456
NA
123456789
999 Carrier Rd
BBBBB
ABC
94612
INS101
CA-DIR
100010009
H14343
12-264
thanks for the Article… but i have a question..
i want the attribute value with the path specified.. for eg:..
1
2
1
2
1
2
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