問題描述
我想從包含基于 where 子句查詢另一個 xml 節(jié)點(diǎn)值的 xml 的 NTEXT 列中獲取一個 xml 節(jié)點(diǎn)值.RDBMS 類型:Microsoft SQL Server T-SQL這里:我想根據(jù) StoreId where 子句值獲取代碼節(jié)點(diǎn)值.我如何得到它?輸入: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:
有關(guān) 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
這篇關(guān)于選擇 SQL 查詢以從 ntext 列中獲取 xml 節(jié)點(diǎn)值?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！