Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

FOR XML PATH - How to remove the element from the output of a FOR XML PATH query?

Aug 4 2008 8:59AM by Jacob Sebastian   

When you write a simple FOR XML query with PATH, you will see that a <row> element will be generated for each row in the result set. For example:

DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'

SELECT Name FROM @t
FOR XML PATH, ROOT ('Employees')

/*
<Employees>
<row>
<Name>Jacob</Name>
</row>
<row>
<Name>Steve</Name>
</row>
</Employees>
*/

Note that a <node> element is created for each row in the query result. PATH is a very powerful operator that allows a great deal of flexibility. Most of the operations previously possible only with EXPLICIT is now possible with PATH. Let us see a few simple variations of the above query and see how we could control the format of the output.

Let us first of all, remove the <node> element.

DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'

SELECT Name FROM @t
FOR XML PATH(''), ROOT ('Employees')
/*
<Employees>
<Name>Jacob</Name>
<Name>Steve</Name>
</Employees>
*/

Now, Let us put each employee under an <Employee> Node.

DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'

SELECT Name FROM @t
FOR XML PATH('Employee'), ROOT ('Employees')
/*
<Employees>
<Employee>
<Name>Jacob</Name>
</Employee>
<Employee>
<Name>Steve</Name>
</Employee>
</Employees>
*/

Finally, lets us change the <Name> element to an attribute.

DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'

SELECT Name AS '@Name' FROM @t
FOR XML PATH('Employee'), ROOT ('Employees')
/*
<Employees>
<Employee Name="Jacob" />
<Employee Name="Steve" />
</Employees>
*/

Is there a FOR XML query that you find hard to write? Send me a note and I will try to help you out.

FOR XML Tutorials

Tags: XML, FOR_XML, FOR_XML_PATH,


Jacob Sebastian
1 · 100% · 32220
1
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"FOR XML PATH - How to remove the element from the output of a FOR XML PATH query?" rated 5 out of 5 by 1 readers
FOR XML PATH - How to remove the element from the output of a FOR XML PATH query? , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]