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)
pinaldave
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……
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…………
[...] 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 [...]
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