CODEWITHSUNDEEP
sql-serverxml
user337390
user337390

Reputation: 131

How to query on XML column In SQL Server 2008

I am using SQL Server 2008

I have a table with xml data looks like:

ID           xml_col  
1            <Attribute id="7">1.0</Attribute><Attribute id="8">AAA</Attribute>  
2            <Attribute id="7">2.0</Attribute><Attribute id="8">BBB</Attribute>  
3            <Attribute id="7">3.0</Attribute><Attribute id="8">AAA</Attribute>  
4            <Attribute id="7">1.0</Attribute><Attribute id="8">BBB</Attribute>  
5            <Attribute id="7">1.0</Attribute><Attribute id="8">AAA</Attribute>  
6            <Attribute id="7">7.0</Attribute><Attribute id="8">CCC</Attribute>

I want to get those records that 

'when attribute=17 then value =1'+ 'when attribute=8 then value='AAA''

How can i create the query and how to build xml indexes?

Thank you very much.

Upvotes: 4

Views: 13783

Answers (1)

marc_s
marc_s

Reputation: 754258

To select all rows which have the criteria you mentioned, try this select statement:

SELECT * 
FROM dbo.YourXmlTable
WHERE
YourXmlTable.xml_col.value('(//Attribute[@id=7])[1]', 'decimal') = 1.0
AND 
    YourXmlTable.xml_col.value('(//Attribute[@id=8])[1]', 'varchar(10)') = 'AAA'

For XML indices, read up on Books Online how to create them and how to use them.

Upvotes: 5

Related Questions