How to Query a Value in SQL Server Xml Column

How can I query a value in SQL Server XML column

select
  Roles
from
  MyTable
where
  Roles.value('(/root/role)[1]', 'varchar(max)') like 'StringToSearchFor'

In case your column is not XML, you need to convert it. You can also use other syntax to query certain attributes of your XML data. Here is an example...

Let's suppose that data column has this:

... and you only want the ones where CodeSystem = 2 then your query will be:

select 
  [data] 
from
  [dbo].[CodeSystemCodes_data]
  
where
  CAST([data] as XML).value('(/Utilities.CodeSystems.CodeSystemCodes/@CodeSystem)[1]', 'varchar(max)') = '2'

These pages will show you more about how to query XML in T-SQL:

Querying XML fields using t-sql

Flattening XML Data in SQL Server

EDIT

After playing with it a little bit more, I ended up with this amazing query that uses CROSS APPLY. This one will search every row (role) for the value you put in your like expression...

Given this table structure:

create table MyTable (Roles XML)

insert into MyTable values
('
   Alpha
   Gamma
   Beta
')

We can query it like this:

select * from 

(select 
       pref.value('(text())[1]', 'varchar(32)') as RoleName
from 
       MyTable CROSS APPLY

       Roles.nodes('/root/role') AS Roles(pref)
)  as Result

where RoleName like '%ga%'

You can check the SQL Fiddle here: http://sqlfiddle.com/#!18/dc4d2/1/0

Extracting a value from an XML column in SQL Server

Your XML has multiple namespaces - 17 total. Just two of them should be taken into account. It is better not to use namespace wildcards due to performance reasons.

Here is how to shred your XML and retrieve what you need.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, payload NVARCHAR(MAX));
INSERT INTO @tbl (payload) VALUES
(N'
             xmlns:ns2="http://example.com/pc/gx/abc.pc.dm.gx.shared.location.addressmodel"
             xmlns:ns4="http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.officialidmodel"
             xmlns:ns3="http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.contactaddressmodel"
             xmlns:ns6="http://example.com/pc/gx/abc.pc.dm.gx.base.account.abc"
             xmlns:ns5="http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.accountcontactmodel"
             xmlns:ns8="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.usermodel"
             xmlns:ns7="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.historymodel"
             xmlns:ns13="http://example.com/pc/gx/abc.pc.dm.gx.base.account.abc"
             xmlns:ns9="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.activitymodel"
             xmlns:ns12="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.industrycodemodel"
             xmlns:ns11="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.documentmodel"
             xmlns:ns10="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.groupmodel"
             xmlns:ns17="http://example.com/pc/gx/abc.pc.dm.gx.shared.producer.producercodemodel"
             xmlns:ns16="http://example.com/pc/gx/abc.pc.dm.gx.shared.producer.accountproducercodemodel"
             xmlns:ns15="http://example.com/pc/gx/abc.pc.dm.gx.shared.general.notemodel"
             xmlns:ns14="http://example.com/pc/gx/abc.pc.dm.gx.base.account.abc">
    
        
            999-01-02T12:00:00-05:00
            ABC
            F
            ABC
            9999-9999-9999
            AA
            S
            individual
        
        9999999999
        
            99 ABC St
            home
            AAA
            AA
            ABC MMM
            AA
            Address
        
        1
        person
    
');
-- DDL and sample data population, end

;WITH XMLNAMESPACES (DEFAULT 'http://example.com/pc/gx/abc.pc.dm.gx.shared.contact.contactmodel'
    , 'http://example.com/pc/gx/abc.pc.dm.gx.base.account.abc' AS ns14) ,rs AS
(
   SELECT id, TRY_CAST(payload AS XML) AS xmldata
   FROM @tbl
)
SELECT ID
   , c.value('(FirstName/text())[1]','VARCHAR(50)') AS FirstName
   , c.value('(LastName/text())[1]','VARCHAR(50)') AS LastName
FROM rs CROSS APPLY rs.xmldata.nodes('/ns14:Account/ns14:AccountHolderContact/entity-Person') AS t(c);

Output

+----+-----------+----------+
| ID | FirstName | LastName |
+----+-----------+----------+
|  1 | ABC       | ABC      |
+----+-----------+----------+

How do I extract a value from an XML column in SQL Server

You can use the below query. Make sure the column SCORERESULTS is of type XML.

SELECT SCORERESULTS.value('(ScoringEngine/Profile/Tool/Rule/Row/Column)[1]', 'nvarchar(MAX)') AS result
FROM CSE_ARCHIVEDCREDITSCORE

If the column is not of type XML use the below query

SELECT CAST(SCORERESULTS AS XML).value('(ScoringEngine/Profile/Tool/Rule/Row/Column)[1]', 'nvarchar(MAX)') AS result
FROM CSE_ARCHIVEDCREDITSCORE

How to query SQL Server XML column

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Parameters XML NOT NULL);
INSERT INTO @tbl (Parameters) VALUES
(N'
  
    Member_Claim_Id
    1802538
  
  
    Reverify
    0
  
  
    RequestNumber
    First Request
  
');
-- DDL and sample data population, end

DECLARE @param VARCHAR(30) = 'RequestNumber';

SELECT ID
    , c.value('(Key/text())[1]', 'VARCHAR(30)') AS [Key]
    , c.value('(Value/text())[1]', 'VARCHAR(30)') AS [Value]
FROM @tbl
CROSS APPLY Parameters.nodes('/ArrayOfItem/Item[Key[text()=sql:variable("@param")]]') AS t(c);

Output

+----+---------------+---------------+
| ID |      Key      |     Value     |
+----+---------------+---------------+
|  1 | RequestNumber | First Request |
+----+---------------+---------------+

Select a value from an XML field in SQL Server

If you need nothing more than the Value to a given Key you can try it like this:

DECLARE @xml XML=
N'
        
            
                
                    
                        
                            A
                            123
                        
                        
                            B
                            456
                        
                        
                            C
                            789
                        
                    
                
            
        
    ';

--directly (hardcoded)
SELECT @xml.value(N'(//KeySet[Key="B"]/Value/text())[1]','int');

--Pass the key through a variable
DECLARE @SearchFor VARCHAR(100)='B';
SELECT @xml.value(N'(//KeySet[Key=sql:variable("@SearchFor")]/Value/text())[1]','int');

In general it is best to avoid the deep search with //. The advise is: Be as specific as possible. So the best (and fastest) was:

SELECT @xml.value(N'(/Dictionary/Keys/GenericKeys/GenericKey/KeySets/KeySet[Key=sql:variable("@SearchFor")]/Value/text())[1]','int');

Fetch a value from a column which contains XML

Use of the value is not done correct, you do:

Y.value('@id','int')

This should be: Y.value('(@id)[1]','int')

round braces around @id, see: docs: value() Method

and Y.value('item[1]','varchar[3]').

This should be: Y.value('(@item)[1]','varchar(3)').

The @ is removed because item is not an attribute
varchar should have round braces, not square braces.

Your try, after changes will become:

select 
   Y.value('(item)[1]','varchar(3)') as valT 
from tbl_storeXML s 
cross apply s.Questions.nodes('Questions/item') as X(Y) 
where e.empId=256 and Y.value('(@id)','int')=101

This is not tested, because I do not have those tables. (I do think Y.value('(item)[1]','varchar(3)') might need to be written as Y.value('(.)[1]','varchar(3)') )

But the same approach can be seen in this DBFIDDLE

DECLARE @xml XML = '
 Yes
 No
';

select 
  X.y.value('(@id)[1]','VARCHAR(20)') id,
  X.y.value('(.)[1]','VARCHAR(20)') value
from  @xml.nodes('Questions/item') as X(y);

output:

id	value
101	Yes
100	No

SQL SERVER: concatenate node values of XML column

select ID, Label,
    stuff(
        details.query('for $step in /terms/destination/email/text() return concat(", ", string($step))')
        .value('.', 'nvarchar(max)'),
    1, 2, '')
from @tbl;