Query XML Column

Nick Riddy 20 Reputation points
2023-05-10T13:47:05.32+00:00

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

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,106 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,476 Reputation points
    2023-05-10T14:05:42.5366667+00:00

    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);
    
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Nick Riddy 20 Reputation points
    2023-05-10T14:07:44.9833333+00:00

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

    0 comments No comments