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 Sir,
i tried to run this Script but the foloowing error is generated.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘‘’.
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……
Thank you Ashish, your above sql statement helped me a lot.
Once again thanks a lot…. :)
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…………
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
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
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
Understandable code.. and very useful to learn about creating XML by using SQL server.
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
Hi,
Text file can be imported using Import/Export wizard or SSIS package.
Regards,
Pinal Dave
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.
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?
hi pinal,
Excellent article, i just become fan of u.
u r a real ‘SQLGURU’
Hi Pinal,
I have an issue while trying to import data from DB2; the column is varchar
Eg
{1:F01SLHIUS3HAXXX4111157995}{2:O9100409080719CITIGB2LGCB220317896570807182309N}{3:{108:000822EB70358CSW}}{4:
:20:3823248701
:21:HAAB2K0162SLII2L
:25:10640417
:32A:080721CHF810000,
:52D:ORDER BANK: UBSWCHZH80A 
UBS AG 
RELATED REF: HAAB2K0162SLII2L
:56D:NO INFORMATION AVAILABLE
:72:NO FURTHER DETAILS
-}{5:{CHK:8186E6AB85DE}}
Now this column has encodeing in ISO-8859-1; I need to import this in SQL Server 2005. I tried the following when trying to im,port using the SQL server Import wizard:
1. Used target column in SQL server as “xml” datatype but it failed saying that the encoding was different; This failed.
2. Imported in target column “varbinary(max)” ; this was successful; but no clue about how to proceed next or convert that into ‘readable’ xml!
Its a huge table – 5 lakh rows!
Is there
How can i write a xml file from the table example Northwind database , categories table , i like to make a xml file to specific path like c:\ from this table in sql 2005
Hello Bernie,
To get the output of SELECT statment in XML format, use FOR XML clause.
To create a new file for each query the easiest method seems using SQLCMD. SQLCMD has options to save data in file and you can create new file using variable to define saperate name to each file.
Regards,
Pinal Dave
Hello Pinal,
I used your code. Its very useful for me to generate XML direct form Sqlserver 2005. I have a problem that how can I manage XML file? means how can I give appropriate name and appropriate path for XML file? Bcoz I need this file for online transection from Desktop application.
Thanks in advance. I am waiting for your reply.
Thanks.
Hello Vijay,
To store XML data, The better is create a table having a XML datatype column and store the xml data there.
Otherwise you can use OUT clause of SQLCMD uitlity to store a result in a file.
Regards,
Pinal Dave
Hello Pinal,
Thanks a lot. Your guidance is very helpful to me. Keep up dude. :)
Hi ,
I tried to import xml file to table.I wrote query and executed , it returend with empty rows.
My XML File
——————-
0101_0102_20100419_153045.xml
0101
0000
04192010 12:00:00 AM
Kayes
1005
Ahmed
Reza
May
Arrear Pay
4501
5009.50
1006
Karim
Uddin
July
Festival Bonus
4713
11050.60
0420
Asifur
Rahman Khan
January
Arrear Pay
1234
5999.99
SQL Query:
——————-
SELECT X.XMLTABLE.query(‘EMPLOYID’).value(‘.’, ‘VARCHAR(15)’),
X.XMLTABLE.query(‘FRSTNAME’).value(‘.’, ‘VARCHAR(15)’),
X.XMLTABLE.query(‘LASTNAME’).value(‘.’, ‘VARCHAR(21)’),
X.XMLTABLE.query(‘CW_MONTHOFSALARY’).value(‘.’, ‘VARCHAR(15)’),
X.XMLTABLE.query(‘CWSALARYTYPE’).value(‘.’, ‘varchar(15)’),
X.XMLTABLE.query(‘CW_SALARYCOMPONENT’).value(‘.’, ‘VARCHAR(15)’),
X.XMLTABLE.query(‘TOTAL’).value(‘.’, ‘money’)
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(
BULK ‘C:\Calsoft\GPPayrollIntegration\ToGP\PayrollXMLOutput_org.xml’,
SINGLE_BLOB) AS T(x)
) AS T(x)
CROSS APPLY x.nodes(‘Tables/Table/Record’) AS X(XMLTABLE)
Thanks in advance.
I am waiting for your reply.
Regards,
Peter
Hi ,
I tried to import xml file to table.I wrote query and executed , it returend with empty rows.
My XML File
——————-
“{
0101_0102_20100419_153045.xml
0101
0000
04192010 12:00:00 AM
Kayes
1005
Ahmed
Reza
May
Arrear Pay
4501
5009.50
1006
Karim
Uddin
July
Festival Bonus
4713
11050.60
0420
Asifur
Rahman Khan
January
Arrear Pay
1234
5999.99
}”
SQL Query:
——————-
SELECT X.XMLTABLE.query(‘EMPLOYID’).value(‘.’, ‘VARCHAR(15)’),
X.XMLTABLE.query(‘FRSTNAME’).value(‘.’, ‘VARCHAR(15)’),
X.XMLTABLE.query(‘LASTNAME’).value(‘.’, ‘VARCHAR(21)’),
X.XMLTABLE.query(‘CW_MONTHOFSALARY’).value(‘.’, ‘VARCHAR(15)’),
X.XMLTABLE.query(‘CWSALARYTYPE’).value(‘.’, ‘varchar(15)’),
X.XMLTABLE.query(‘CW_SALARYCOMPONENT’).value(‘.’, ‘VARCHAR(15)’),
X.XMLTABLE.query(‘TOTAL’).value(‘.’, ‘money’)
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(
BULK ‘C:\Calsoft\GPPayrollIntegration\ToGP\PayrollXMLOutput_org.xml’,
SINGLE_BLOB) AS T(x)
) AS T(x)
CROSS APPLY x.nodes(‘Tables/Table/Record’) AS X(XMLTABLE)
Thanks in advance.
I am waiting for your reply.
Regards,
Peter