Get XML root name in Sql Server

declare @.x xml
set @.x =
'<Chicago>
<Area>A1</Area>
<Group>5</Group>
<Question>Q1</Question>
</Chicago>'

How to get the XML root name 'Chicago' in Sql Server 2005?

Since you are using the XQuery or XPath query you should know the exact path of the xml. Otherwise you can't utilize this new feature in SQL Server...

There is no functions available in XQuery to fetch the XML Root element name, but you can utilize the Stringmanipulation to find what is your root..

Code Snippet

declare @.x xml

set @.x =

'<Chicago>

<Area>A1</Area>

<Group>5</Group>

<Question>Q1</Question>

</Chicago>'

Select

Substring(XML,Charindex('<', XML)+1, Charindex('>', XML) - Charindex('<', XML)-1)

From

(

Select

Cast(@.X as Varchar(max)) as XML

) as Data

Get the Names for different levels in a table

hi

I've a table with coln names

ID
Name
ParentID
Level

I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4

How to write the query for getting the Names for different levels for corresponding ParentID....

Output should be like:-

Leaf Branch Trunk Root

Stem Branch Trunk Root

I think you may want to check out hierarchical queries in Books Online. Here is a link with a good example I think you can adjust. http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

Get the Names for different levels in a table

hi

I've a table with coln names

ID
Name
ParentID
Level

I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4

How to write the query for getting the Names for different levels for corresponding ParentID....

Output should be like:-

Leaf Branch Trunk Root

Stem Branch Trunk Root

I think you may want to check out hierarchical queries in Books Online. Here is a link with a good example I think you can adjust. http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm