I have a table that stores data about errors that occur (via SSIS) , the main column that has information is stored as XML. I want to be able to query name/value pairs in this column.
To explain in SQL->(Select * from #tmp Where seq='7406834')
CREATE TABLE #tmp(id INT, category varchar(10), details xml)
INSERT INTO #tmp(id,category,details) values (1,'cat1','<fields><field name="brnum" value="586" /><field name="qty" value="0" /><field name="seq" value="7406815" /></fields>')
INSERT INTO #tmp(id,category,details) values (1,'cat2','<fields><field name="brnum" value="586" /><field name="qty" value="0" /><field name="seq" value="7406817" /></fields>')
INSERT INTO #tmp(id,category,details) values (1,'cat3','<fields><field name="brnum" value="586" /><field name="qty" value="0" /><field name="seq" value="7406834" /></fields>')
INSERT INTO #tmp(id,category,details) values (1,'cat4','<fields><field name="brnum" value="586" /><field name="qty" value="0" /><field name="seq" value="7406841" /></fields>')
DROP TABLE #tmp
Can anyone help with syntax. I have been able to essentially use a derived table, making each element into a column, and then query that, but it seems there should be a more simplistic way to say give me all the attributes (id, category, details) for each row that have a details.seq='xxxxx'.
thanks.