Query XML Column

Question

I've got a table with a xml column which can contain multiple resourceid's
I would like to select the Values From ResourceIDXML like this:
Select Value From SchedulerTD.ResourceIDXML Where ID = 3

And the result should be :
1129
1131

Answer 1

Hi @Nick Riddy,

Please try the following solution.

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, ResourceIDXML XML);
INSERT @tbl (ResourceIDXML) VALUES
(N'<ResourceIds>
	<ResourceId Type="System.Int32" Value="1129"/>
	<ResourceId Type="System.Int32" Value="1131"/>
</ResourceIds>');
-- DDL and sample data population, end

SELECT id	
	, c.value('.', 'INT') AS ResourceIDXML
FROM @tbl
CROSS APPLY ResourceIDXML.nodes('/ResourceIds/ResourceId/@Value') AS t(c);

Answer 2

dfsd

SQLVersion : Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64) Jan 23 2023 13:08:05 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19045: )