Search code examples
sql-serversqlxml

How to query values from XML without nodes?

I have a XML like this:

<User LoginName="yes" Signature="A" MobilePhone="yes" />

and I use the following query:

SELECT field.value('/User[@LoginName=1][1]','varchar(max)') 
FROM table

I expect the output for LoginName to be "yes"

Solution

  • You can use the following query to get your expected result:

    SELECT R.V.value('@LoginName[1]', 'varchar(max)') AS LoginValue
FROM   TestTable AS T
OUTER APPLY T.data.nodes('User[1]') as R(V)

    Sample execution with some data:

    DECLARE @TestTable TABLE (data XML);

INSERT INTO @TestTable (data) VALUES
('<User LoginName="yes" Signature="A" MobilePhone="yes" />
<User LoginName="no" Signature="B" MobilePhone="NA" />');

SELECT R.V.value('@LoginName[1]', 'varchar(max)') AS LoginValue
FROM   @TestTable AS T
OUTER APPLY T.data.nodes('User[1]') as R(V)

    it will result as 

    LoginValue
----------
yes

    Please find the working demo on db<>fiddle