Using ranking functions on XML column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using ranking functions on XML column

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-04-26 : 01:15:33

i have an xml like the below

DECLARE @XML XML
SET @XML = '<Employee>
<EmployeeDetails>
<EmployeeID>1</EmployeeID>
<EmployeeName>aaa</EmployeeName>
</EmployeeDetails>
<EmployeeDetails>
<EmployeeID>2</EmployeeID>
<EmployeeName>bbb</EmployeeName>
<Employeeadd1>xxx</Employeeadd1>
<Employeeadd2>yyy</Employeeadd2>
</EmployeeDetails>
</Employee>'

SELECT
x.value('local-name(.)', 'VARCHAR(500)') AS FIELDNAME,
x.value('.', 'VARCHAR(500)') AS VALUE
FROM
@XML.nodes('/*/*/*') as n1(x)

how can i use the rank functions to get the result like below

FieldName value rank

EmployeeID 1 1
EmployeeName aaa 1
EmployeeID 2 2
EmployeeName bbb 2
Employeeadd1 xxx 2
Employeeadd2 yyy 2

do we have any other alternative to get the result like this.

Kindly help me

Thanks

Jack

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-26 : 04:11:48

[code]SELECT y.value('local-name(.)', 'VARCHAR(500)') AS FieldName,
y.value('.', 'VARCHAR(500)') AS Value,
DENSE_RANK() OVER (ORDER BY n1.x) AS [rank]
FROM @XML.nodes('/*/*') as n1(x)
CROSS APPLY x.nodes('*') AS n2(y)[/code]

N 56°04'39.26"
E 12°55'05.63"

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-05-02 : 02:10:32

Thanks friend... awesome

Thanks

Jack

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources