How to obtain a XML column which is part of a table field in SQL

Question

I'm using Yellowfin which connects to a SQL 2012 database. Within a table there is a column called EnteredXML which contains full XML from a web submission, so a sub table of XML type within the column. From the XML column I want to obtain a specific value from the column called CoverRateSelection.

I've tried the following:

SELECT 
    MemberNo, 
    TransTypeID, 
    EnteredXML.value('(/MembershipApplication/Cover/CoverRateSelection/@value)[1]', 'varchar(max)') 
FROM 
    V204241Webtransactions
WHERE 
    TransTypeID = 'MemAp'

The response I get from this is

SQL statement is invalid.

The error message returned from the database was:

Cannot find either column "EnteredXML" or the user-defined function or aggregate "EnteredXML.value", or the name is ambiguous.

the table looks like this:

The XML looks like this (I've removed personal info):

I got this far using other posts at stackoverflow:

how-to-get-a-particular-attribute-from-xml-element-in-sql-server

extracting-attributes-from-xml-fields-in-sql-server-2008-table

reading-xml-that-is-saved-as-text-from-sql

but I'm now a bit lost and confused. Any help would be greatly appreciated.

Thanks

Chris

Are you 100% sure on the column name, because that's the most obvious problem that the error message is trying to steer you towards. — Damien_The_Unbeliever, Aug 09 '16 at 06:47
What is the type of `EnteredXML` ? It must be XML to accept the xml function `value`. — Serg, Aug 09 '16 at 07:32

score 0 · Accepted Answer · answered Aug 11 '16 at 02:29

Thanks to @Serg I realised although the values were XML it was infact a Text field, therefore I used the Substring Method to obtain the data:

SELECT 
    MemberNo, 
    TransTypeID, 
    SUBSTRING(EnteredXML, (CHARINDEX('<CoverRateSelection>', EnteredXML)+ 20), (CHARINDEX('</CoverRateSelection>', EnteredXML) - (CHARINDEX('<CoverRateSelection>', EnteredXML)) As JoinCover
FROM 
    V204241Webtransactions
WHERE 
    TransTypeID = 'MemAp'

If you run this type of command however it also brings in the XML tag. I've tried adding a value to the length section of the substring:

((CHARINDEX('</CoverRateSelection>', EnteredXML)-20) - (CHARINDEX('<CoverRateSelection>', EnteredXML)

but it returns no values and says the SQL is invalid, despite the SQL test saying it is valid. I believe this is an issue with the program rather than not the SQL.

How to obtain a XML column which is part of a table field in SQL

1 Answers1