none
Select XML data

    Question

  • Okay, this should be simple, but I can't get it to work. I have XML data in an ntext field (developers, what can you do), that I need to parse out a value from. The data looks like this:

    <UDFValidatedValue xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/stuff.UDF">
      <Description>North America</Description>
      <Value>North America</Value>
      <ValueType>String</ValueType>
    </UDFValidatedValue>

    Now, I wrote my query like this:

     SELECT cast(GeographicRegion as xml).query('/UDFValidatedValue/Description')  as Region
    FROM   CompanyUdf

    But I get a blank for the column instead if the data for Description.

    I tried this:

    SELECT cast(GeographicRegion as xml).value('(/UDFValidatedValue/Description)[1]', 'nvarchar(max)')  as Region
    FROM   CompanyUdf

    and I get a "NULL" value.

    What am I doing wrong?

    Thanks in advance!

    mpleaf

    Friday, February 07, 2014 12:26 AM

Answers

  • Thanks to my current employer I now know more than I ever wanted to about XML and yet I still don't know enough.

    declare @XML varchar(max) = '<UDFValidatedValue xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/stuff.UDF">
      <Description>North America</Description>
      <Value>North America</Value>
      <ValueType>String</ValueType>
    </UDFValidatedValue>'
    
    ;WITH XMLNAMESPACES (default 'http://schemas.datacontract.org/2004/07/stuff.UDF')
    SELECT cast(@XML as xml).query('/UDFValidatedValue/Description')  as Region
    
    ;WITH XMLNAMESPACES (default 'http://schemas.datacontract.org/2004/07/stuff.UDF')
    SELECT cast(@XML as xml).value('(/UDFValidatedValue/Description)[1]', 'nvarchar(max)')  as Region

    That should work for ya.

    Namespaces in your XML make life difficult, in my opinion.

    Friday, February 07, 2014 1:33 AM

All replies

  • Thanks to my current employer I now know more than I ever wanted to about XML and yet I still don't know enough.

    declare @XML varchar(max) = '<UDFValidatedValue xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/stuff.UDF">
      <Description>North America</Description>
      <Value>North America</Value>
      <ValueType>String</ValueType>
    </UDFValidatedValue>'
    
    ;WITH XMLNAMESPACES (default 'http://schemas.datacontract.org/2004/07/stuff.UDF')
    SELECT cast(@XML as xml).query('/UDFValidatedValue/Description')  as Region
    
    ;WITH XMLNAMESPACES (default 'http://schemas.datacontract.org/2004/07/stuff.UDF')
    SELECT cast(@XML as xml).value('(/UDFValidatedValue/Description)[1]', 'nvarchar(max)')  as Region

    That should work for ya.

    Namespaces in your XML make life difficult, in my opinion.

    Friday, February 07, 2014 1:33 AM
  • That got me the result I needed. I didn't do the declare of the variable, but rather used my column name (GeographicRegion) being cast as XML, and it worked like a charm! What can you do when developers put stuff inside XML (with namespaces), and put that inside a ntext field! :)

    Thanks!

    mpleaf

    Friday, February 07, 2014 5:37 PM