CODEWITHSUNDEEP
sql-serverxml
user9020594
user9020594

Reputation: 95

Querying XML Node with a where criteria

I have a table with following attributes:

[Students]

ID Class Data (xml) 1 Secondary XML Data

Below is a sample xml structure that I store on the data attribute.

<Root>
    <Data>
        <Name>John</Name>
        <Rank>1</Rank>
    </Data>
    <Data>
        <Name>Peter</Name>
        <Rank>2</Rank>
    </Data>
</Root>

I want to write a sql query which will give me following data from the table:

ID, Class and Rank 2 student Name My Query is

Select ID,Class,Data.value('(/Root/Data/Name)[1]', 'NVARCHAR(3)')   AS [Rank 2 Student Name] 
      FROM [Students]

This works fine, but hardcoding the the node number to 1 is not a good idea here becuase if I add a new Data node in my xml then the data will change. And this is a possibility. So is there a way I can specify that return Name for the node where Rank value is 2.

Thanks.

Upvotes: 0

Views: 54

Answers (2)

Paweł Dyl
Paweł Dyl

Reputation: 9143

You can apply filter in xml as follows:

DECLARE @Students TABLE(ID int, Class varchar(10), Data xml)

INSERT @Students VALUES (1,'A', '<Root>
    <Data>
        <Name>John</Name>
        <Rank>1</Rank>
    </Data>
    <Data>
        <Name>Peter</Name>
        <Rank>2</Rank>
    </Data>
</Root>')

SELECT ID,Class,Data.value('(/Root/Data[Rank=2]/Name)[1]', 'NVARCHAR(5)') AS [Rank 2 Student Name] 
FROM @Students

Result

ID          Class      Rank 2 Student Name
----------- ---------- -------------------
1           A          Peter

Edit. If Rank contains free text, use quotes:

DECLARE @Students TABLE(ID int, Class varchar(10), Data xml)

INSERT @Students VALUES (1,'A', '<Root>
    <Data>
        <Name>John</Name>
        <Rank>1</Rank>
    </Data>
    <Data>
        <Name>Peter</Name>
        <Rank>0:Company name</Rank>
    </Data>
</Root>')

SELECT ID,Class,Data.value('(/Root/Data[Rank="0:Company name"]/Name)[1]', 'NVARCHAR(5)') AS [Rank 2 Student Name] 
FROM @Students

Upvotes: 0

hkravitz
hkravitz

Reputation: 1385

you can use this:

DECLARE @tbl TABLE (xmlcol xml )
INSERT INTO @tbl
VALUES ('<Root>
    <Data>
        <Name>John</Name>
        <Rank>1</Rank>
    </Data>
    <Data>
        <Name>Peter</Name>
        <Rank>2</Rank>
    </Data>
</Root>')

SELECT *
FROM 
(
SELECT r.d.value ('(./Name/text())[1]', 'varchar(50)') [Name],
       r.d.value ('(./Rank/text())[1]', 'varchar(50)') [Rank]
 FROM @tbl
    CROSS APPLY xmlcol.nodes ('/Root/Data') R(d)
) P 
WHERE P.[Rank] = 2

which will output the following result:

Name    Rank
Peter   2

Upvotes: 1

Related Questions