ansaurus

Question

How to query on XML column In SQL Server 2008

Answer 1

A:

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.

marc_s 2010-06-07 09:42:14

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?

2010-06-07 10:25:55

@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.

marc_s 2010-06-07 10:53:53

ansaurus

tags:

views:

answers:

How to query on XML column In SQL Server 2008

related questions