从SQL Server中的XML数据中查找值?

请尝试以下SQL。它显示了如何在 XPath语言 要模拟的表达式 WHERE 条款。

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata)
VALUES
(N'<wddxPacket version="1.0">
    <header/>
    <data>
        <struct type="xyz">
            <var name="TXRGHC43">
                <string/>
            </var>
            <var name="TWBS1">
                <string>9011750</string>
            </var>
            <var name="PMNAMEID">
                <string>2323443</string>
            </var>
            <var name="EDATE36">
                <string/>
            </var>
            <var name="TWBSDESC40">
                <string/>
            </var>
        </struct>
    </data>
</wddxPacket>');
-- DDL and sample data population, end

DECLARE @varName VARCHAR(20) = 'TWBS1';

SELECT c.value('(./text())[1]','INT') AS [string_value]
FROM @tbl AS tbl
    CROSS APPLY tbl.xmldata.nodes('/wddxPacket/data/struct/var[@name=sql:variable("@varName")]/string') AS t(c);

输出

+--------------+
| string_value |
+--------------+
|      9011750 |
+--------------+