使用Xquery按XML类型字段从SQL Query返回的过滤行

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.

我有一个表存储有关发生的错误的数据(通过SSIS),具有信息的主列存储为XML。我希望能够在此列中查询名称/值对。

To explain in SQL: SELECT * FROM #tmp WHERE seq='7406834'

在SQL中解释:SELECT * FROM #tmp WHERE seq ='7406834'

Example table:

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

任何人都可以帮助语法。我已经能够基本上使用派生表,使每个元素成为一个列,然后查询,但似乎应该有一个更简单的方式来说明给我每一行的所有属性(id,类别,细节)有一个details.seq ='xxxxx'。

1 个解决方案

#1

Changed my mind, and used FLWOR :P

改变了我的想法,并使用了FLWOR:P

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>'
        )

DECLARE @val INT = 7406834 


SELECT  *
FROM    #tmp t
WHERE   details.value('(for $f in //field
                        where data($f/@name) = "seq"
                        return 
                          data($f/@value))[1]', 'int') = @val


DROP TABLE #tmp