Menu
I have a query that creates an xml file. Currently, I have the ROOT element hard-coded. I would like to use a variable value to set this root element value but substituting the hard-coded string with the variable throws a syntax error of expecting a string. This is the select statement:
DECLARE @SelectResults XML
DECLARE @DatabaseName varchar(100)
SELECT @DatabaseName = DB_NAME();
SET @SelectResults =
(
SELECT...query results here...
FOR XML PATH(''),
ROOT(@DatabaseName) --when this is set to 'DatabaseName' it works
)
Can I use a variable in the function ROOT()?
676
A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.
You should use the query() Method if you want to get a part of your XML. If you want the value from a specific node you should use value() Method. Update: If you want to shred your XML to multiple rows you use nodes() Method.
The FOR-clause has the following modes that allow you to render a relational and/or tabular structure into a hierarchical XML structure – elements, attributes, entity encoding and so on: Recordset. Raw. Auto.
Adding the FOR XML PATH(''), TYPE converts these rows into a single strongly-typed XML text node with the concatenated rows, allowing XML methods like value to be used on the type: select ',' + quotename (C.name) from sys.columns c where c.object_id = OBJECT_ID('dbo.result2') for xml path(''), TYPE.
You could do the replacement in a separate replace acting on the XML output:
DECLARE @SelectResults XML
DECLARE @DatabaseName varchar(100)
SELECT @DatabaseName = DB_NAME();
SET @SelectResults =
replace(
SELECT...query results here...
FOR XML PATH(''),
ROOT('ROOT_ELEMENT') --when this is set to 'DatabaseName' it works
), 'ROOT_ELEMENT>',@DatabaseName+'>' )
Here is a little cheat using concat()
Create some sample data
Declare @SomeTable table (id int,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))
Insert into @SomeTable values
(1,'John','Smith','[email protected]'),
(2,'Jane','Doe' ,'[email protected]')
The SQL
Declare @SelectResults XML = concat('<',DB_NAME(),'>',(Select * from @SomeTable for XML Path),'</',DB_NAME(),'>')
Select @SelectResults
Returns
<Chinrus-Dev>
<row>
<id>1</id>
<First_Name>John</First_Name>
<Last_Name>Smith</Last_Name>
<EMail>[email protected]</EMail>
</row>
<row>
<id>2</id>
<First_Name>Jane</First_Name>
<Last_Name>Doe</Last_Name>
<EMail>[email protected]</EMail>
</row>
</Chinrus-Dev>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
