Query XML Attribute values in WHERE clause

Question

I have the following xml Data in an XML column

<LoyaltyInfo ServerDate="2056-12-12">
  <Accounts>
    <Acc ID="1" Value="2.750" UpToDate="2014-05-13 18:38:00" />
    <Acc ID="2" Value="2.750" UpToDate="2014-05-13 18:38:00" />
    <Acc ID="3" Value="141.06" UpToDate="2014-05-22 12:53:00" />
    <Acc ID="6" Value="924.42" UpToDate="2014-06-01 16:53:00" />
    <Acc ID="7" Value="2895" UpToDate="2014-05-13 18:38:00" />
 </Accounts>
</LoyaltyInfo>

I want to write an SQL query to say WHERE Acc ID = x and in the same row Acc Value = y

Ive tried the following but i get errors

    SELECT *
    FROM TableName
    WHERE xmlData.exist('/LoyaltyInfo/Accounts/Acc[@ID="20"]') = 1 
    AND xmlData.value('/LoyaltyInfo/Accounts/Acc/@Value','integer') = 0

SELECT *
FROM TableName
WHERE xmlData.exist('/LoyaltyInfo/Accounts/Acc[@ID="20"]') = 1 
AND   xmlData.value('/LoyaltyInfo/Accounts/Acc[@Value]','integer') = 0

valex · Accepted Answer · 2014-06-02 07:17:58Z

1

Try this query using fn.min() function to emulate AND operator:

select *
FROM T
WHERE xmldata.value('fn:min((LoyaltyInfo/Accounts/Acc/@ID="3",
              LoyaltyInfo/Accounts/Acc/@Value="141.06"))','bit')=CAST(1 as bit)

SQLFiddle demo

answered Jun 2, 2014 at 7:17

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anthony · Accepted Answer · 2014-06-04 23:38:10Z

0

FYI for anyone else looking in the future

For 1 condition

select xmldata.query('for $loyaltyinfo in /LoyaltyInfo/Accounts                          
let $id :=$loyaltyinfo/Acc/@ID
let $value := $loyaltyinfo/Acc/@Value
where $id = "19" and $value = "141.06"
return $loyaltyinfo
') as [Valid Data]
FROM T

For 2 conditions

select xmldata.query('for $loyaltyinfo in /LoyaltyInfo/Accounts                          
let $id :=$loyaltyinfo/Acc/@ID
let $value := $loyaltyinfo/Acc/@Value
where ($id = "19" and $value = "141.06") and ($id = "20" and $value = "0.00")
return $loyaltyinfo
') as [Valid Data]
FROM T

answered Jun 4, 2014 at 23:38

Anthony

976 bronze badges

Collectives™ on Stack Overflow

Query XML Attribute values in WHERE clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related