1

I have done a lot of researching on the net for the answer to this question. I have found a lot of help sites but I'm not getting the results unfortunately (I don't really understand XML).

I have a table (called CustomField.PersonCustomFieldValue) that has an XML datatype column (called XmlValue) which stores XML data. I am unsure how to extract the values.

An example of the data stored in the column is:

<XmlDataValue xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Value xsi:type="xsd:string">NO</Value></XmlDataValue>

How do extract the Value string? In this case - "No".

Thanks very much.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
Mark Bell
  • 25
  • 2
  • Check [this](http://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008) question, might help you through! – N00b Pr0grammer Feb 13 '17 at 09:01

1 Answers1

0

Try it like this:

DECLARE @xml XML=
N'<XmlDataValue xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Value xsi:type="xsd:string">NO</Value>
</XmlDataValue>';
SELECT @xml.value(N'(/XmlDataValue/Value/text())[1]',N'nvarchar(max)');

This will return the same

SELECT @xml.value(N'(//Value)[1]',N'nvarchar(max)')

But you should always be as specific as possible!

Calling this for a table's column looks like this:

SELECT XmlValue.value(N'(/XmlDataValue/Value/text())[1]',N'nvarchar(max)')
FROM YourTable
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Many many thanks Shnugo. Another question, how could I adjust the column SQL to only display a certain value without nesting the Select statement in another Select? – Mark Bell Feb 13 '17 at 10:33
  • @MarkBell One principle on SO is: One issue - one question. Please avoid *follow-up* questions. Please start a new question, where you place some sample code and the the expected output. You must provide a more information. I have no idea, what other selects you have, where you think to nest something... Happy Coding! – Shnugo Feb 13 '17 at 10:43