选择 SQL 查询以从 ntext 列中获取 xml 节点值?

2021-10-02 00:00:00 xml xpath sql sql-server

我想从包含基于 where 子句查询另一个 xml 节点值的 xml 的 NTEXT 列中获取一个 xml 节点值.RDBMS 类型:Microsoft SQL Server T-SQL这里:我想根据 StoreId where 子句值获取代码节点值.我如何得到它?输入:100输出:ABCDE

I want to get one xml node value from NTEXT column which contains xml based on where clause quering on another xml node value. RDBMS Type: Microsoft SQL Server T-SQL Here: I want to get Code node value based on StoreId where clause value. How do I get it? Input: 100 Output:ABCDE

例如:

<root>
  <StoreProfile>
    <General>
     <StoreId>100</StoreId>
     <Code>ABCDE</Code>
    </General>
  </StoreProfile>
</root>

推荐答案

如果您使用的是 SQL Server 2005 或 2008，您可以像这样使用 XQuery:

If you are using SQL Server 2005 or 2008 you can use XQuery like so:

有关 XQuery 的更多信息，请参阅 XQuery 语言参考

For more on XQuery see XQuery Language Reference

DECLARE @storeId INT
SET @storeId = 100

CREATE TABLE #TestTable
(
    xmlColumn NTEXT
)

INSERT INTO #TestTable (xmlColumn) Values('<root><StoreProfile><General><StoreId>100</StoreId><Code>ABCDE</Code></General></StoreProfile></root>')
INSERT INTO #TestTable (xmlColumn) Values('<root><StoreProfile><General><StoreId>200</StoreId><Code>FGHIJ</Code></General></StoreProfile></root>')

SELECT 
    StoreProfile.value('Code[1]', 'nvarchar(10)') as Code 
FROM #TestTable
    CROSS APPLY (SELECT CAST(xmlColumn AS XML)) AS A(B) 
    CROSS APPLY A.B.nodes('//root/StoreProfile/General[StoreId = sql:variable("@storeId")]') AS StoreProfiles(StoreProfile)

DROP TABLE #TestTable

相关文章