SQL SERVER – Simple Example of Creating XML File Using T-SQL « Journey to SQL Authority with Pinal Dave

SQL SERVER – Simple Example of Creating XML File Using T-SQL

February 12, 2009 by pinaldave

I always want to learn SQL Server and XML. Let us go over 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 other article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged SQL XML | 12 Comments

12 Responses

on February 16, 2009 at 7:49 am | Reply puneet

Hello Sir,

i tried to run this Script but the foloowing error is generated.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘‘’.
on February 16, 2009 at 1:29 pm | Reply Ashish

Thanks for the Article ,,,,,,,,,,,,,,,
But if we want to create an xml from Datatable den u can also use this one to create XML:::::

SELECT * FROM URTABLE FOR XML RAW

SELECT * FROM URTABLE FOR XML AUTO

select * from URTABLE for Xml RAW,elements,root(‘Sample’)

select * from test1 for Xml AUTO,elements,root(‘Sample’)

And if u want to export that XML Some where than use BCP option to Export XML data to file…

Thanks……
on February 17, 2009 at 12:48 pm | Reply Ashish

Dont just copy and paste that script in to Query Window….
u have to replace all the single quotes manually…..
this will remove ur error…………
on February 26, 2009 at 5:08 pm | Reply SQL SERVER - Simple Example of Reading XML File Using T-SQL Journey to SQL Authority with Pinal Dave

[...] one of the previous article we have seen how we can create XML file using SELECT statement SQL SERVER – Simple Example of Creating XML File Using T-SQL. Today we will see how we can read the XML file using the SELECT [...]
on August 4, 2009 at 4:59 pm | Reply Shital Kasliwal

hello sir,

following xml query is valid sql statemant?

SELECT cast(((
SELECT *
FROM PatientDetails pd
WHERE pd.PatientDetailId = 1
FOR xml path,
root(‘PatientDetails’)
)+
isnull((SELECT *
FROM PatientEncounters pe
WHERE pe.PatientDetailId = 1
FOR xml path,root(‘PatientEncounters’)),(SELECT dbo.get_table_xml_structure1_fun
(‘PatientEncounters’,'dbo’) gtxsf) )) AS xml)

if valid pls coorect this
on September 23, 2009 at 11:58 am | Reply Prathees

Hello Sir,

Your blog always helpful to me and hope also for all.

I got the XML using FOR XML concept. but i need to save it using Query in future also update
on October 27, 2009 at 6:56 pm | Reply Maunang

Hi Pinal,

The following query took 10 minutes to insert with 2M xml input. However, if we take the insert statement out it seems to run instantly. Also, if we change ‘elements’ to ‘attributes’ in the xml it executes within seconds.
Any suggestions??

declare @RTT_Out_Xml xml

declare @MyRTTOutput table (EntityID char(16), Binder_ID char(16), Acct_Number varchar(18), Amount float,
UnitCode varchar(64), UnitName varchar(256), SrcMultiplier float, SrcAmount float, SourceFields xml, Error xml)

set @RTT_Out_Xml = (select EntityID, Binder_ID, Acct_Number, Amount, UnitCode, UnitName SrcMultiplier, SrcAmount
from GSNRTTOutput
for xml path (‘Target’), type, root (‘TargetBalance’) )

insert into @MyRTTOutput (EntityID, Acct_Number, Amount, UnitCode, SrcMultiplier, SrcAmount)
select a.b.query(‘EntityID’).value(‘.’,'varchar(16)’) as EntityID,
a.b.query(‘Acct_Number’).value(‘.’,'varchar(18)’) as Acct_Number,
a.b.query(‘Amount’).value(‘.’,'float’) as Amount,
a.b.query(‘UnitCode’).value(‘.’,'varchar(64)’) as UnitCode,
a.b.query(‘SourceMultiplier’).value(‘.’,'float’) SrcMultiplier,
a.b.query(‘SrcAmount’).value(‘.’,'float’) as SrcAmount
from @RTT_Out_Xml.nodes (‘/TargetBalance/Target’) a(b)

select * from @MyRTTOutput
on November 27, 2009 at 11:39 am | Reply Resham

Understandable code.. and very useful to learn about creating XML by using SQL server.
on December 22, 2009 at 2:21 pm | Reply RS Pandey

This is not comment .. This is my problem .. If u r interested to solve..How to insert data from *.TAXT file to Database of Sql Server
Text File
99999 XXXXXXXXXX 99999.99
99999 XXXXXXXXXX 99999.99
99999 XXXXXXXXXX 99999.99

Database Structure
Code Numeric 5
Name Char 10
Amt money 8
on December 23, 2009 at 10:10 am | Reply Pinal Dave

Hi,

Text file can be imported using Import/Export wizard or SSIS package.

Regards,
Pinal Dave
on February 1, 2010 at 4:07 pm | Reply Sandeep

Hi Pinal,

I need one help, i am geting data from sql server in XMl format but need to export those data in to XML files.

Can you help me to fix this.

Thanks in advance.
on February 8, 2010 at 9:20 pm | Reply Gurmeet Singh

The problem with this type of XML Export is that you cannot open in Excel file, if you try to open you get failed to parse error. How do we resolve this?