Parsing XML in SQL, atypical attribute format

Question

This XML format is a given (it comes from an app my company runs):

<User display="User">NAME1</User>

So I've been running the following code to try and tease out the true value (NAME1) from this format:

declare @xml xml = '<User display="User">NAME1</User>'
select @xml.value('(User/@display)[1]', 'nvarchar(max)') as USER_NM

I'm using SQL Server 2012. But nothing I try can pick out the NAME1, rather than User. Any ideas?

marc_s · Accepted Answer · 2016-05-05 20:23:51Z

1

Just use this snippet instead:

SELECT @xml.value('(User)[1]', 'nvarchar(max)') as USER_NM

This will read out the textual value of the <User> element - NAME1 in your case

answered May 5, 2016 at 20:23

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1