How to query on XML column In SQL Server 2008

Question

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.

If you want to post code or XML, please highlight those lines and click on the "code" button (101 010) on the editor toolbar. That formats the code/XML nicely, and provides syntax highlighting, and you don't have to mess around with your XML codes (no need to replace the < and > characters)! Do not use <pre> - doesn't work here — marc_s
– marc_s, Commented Jun 7, 2010 at 9:37

marc_s · Accepted Answer · 2010-06-07 09:42:14Z

5

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.

answered Jun 7, 2010 at 9:42

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

2 Comments

user337390 Over a year ago

Thank you very much. Base on your information, I have created indexes as below: CREATE PRIMARY XML INDEX idx_xCol on TABLEA (XML_col) GO CREATE XML INDEX idx_xCol_Path on TABLEA (XML_col) USING XML INDEX idx_xCol FOR PATH GO CREATE XML INDEX idx_xCol_Value on TABLEA(XML_col) USING XML INDEX idx_xCol FOR VALUE but the performance looks not very good, any recommendation for indexes?

marc_s Over a year ago

@envykok: if you have an XML index for everything, that's about all you can do. Reaching into XML using XQuery is not known to be an awfully fast operation..... if you need to query certain elements over and over again, extract them into the relational table structure, instead of having them inside the XML column.

Collectives™ on Stack Overflow

How to query on XML column In SQL Server 2008

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related