CODEWITHSUNDEEP
sql-serverxml
user2506589
user2506589

Reputation: 155

Selecting a value from XML data type

How to write a SQL Server query that selects from an xml data type and selects attribute in an element that is matched based on other attribute.

Imagine the following example :

create table dbo.Configuration(Configuration xml not null);

insert into dbo.Configuration(Configuration) values(convert(xml, '<?xml version="1.0" standalone="yes"?>
<Configuration>
    <DoSRequestAnalysis>
        <Windows>
            <Window Name="Smallest" Duration="15">
                <ThresholdsToRemoveRequests NoofRequests="25" />
            </Window>
        </Windows>
    </DoSRequestAnalysis>
</Configuration>
'));

select Configuration.value('(/Configuration/DoSRequestAnalysis/Windows/Window[@Name="Smallest"]/@Duration)[0]', 'smallint') from dbo.Configuration; -- I want to select the value of the attribute Duration i.e. 15 but this select returns null

How to write a query so it selects the Duration attribute ?

Many thanks

Upvotes: 1

Views: 79

Answers (1)

Alex S
Alex S

Reputation: 1221

You are close, just change [0] to [1]

select Configuration.value('(/Configuration/DoSRequestAnalysis/Windows/Window[@Name="Smallest"]/@Duration)[1]', 'smallint')

Upvotes: 1

Related Questions