2

For the usage in a SQL query I need to get the value of a specific XML element. The XML element is specified by its attribute.

My XML elements looks like this:

<translations>
  <value lang="en-US">example</value>
  <value lang="de-DE">Beispiel</value>
</translations>

and the value I am looking for would be "example" when I specify lang to be "en-US".

I found a way to get this value by using the query() function and afterwards the value() function. 

declare @S varchar(max)

set @S =
'<translations>
  <value lang="en-US">example</value>
  <value lang="de-DE">Beispiel</value>
</translations>'

declare @X xml

set @X = CAST(@S as xml)

select @X.query('/translations/value[@lang="en-US"]').value('.','varchar(max)')

This select statement return the value "example" I am looking for by using the query() and value() function. But is there also a - more convenient - way to only use value() OR query()?

Thank you in advance!

Shnugo
  • 66,100
  • 9
  • 53
  • 114
bert
  • 35
  • 1
  • 7

1 Answers1

1

Sure there is...

You also can shorten the declaration:

declare @X xml=
'<translations>
  <value lang="en-US">example</value>
  <value lang="de-DE">Beispiel</value>
</translations>';

select @X.value('(/translations/value[@lang="en-US"])[1]','varchar(max)');

The point is, that you need a singleton result when you are using .value(). You achieve this by putting the XPath in paranthesis and force the first element to be taken (in this case it's the only element).

Btw: If you need this (and sooner or later you will need this...), you might put the "en-US" as parameter into your query like this:

declare @prm VARCHAR(10)='en_US';
select @X.value('(/translations/value[@lang=sql:variable("@prm")])[1]','varchar(max)');

You can reach the similar, but with a value of the actual query by using sql:column().

Shnugo
  • 66,100
  • 9
  • 53
  • 114