SQL Query - Querying XML, having issues

Question

I'm working on a project and need some help trying to get this XML query to work. I'm trying to query the XML below in SQL as a new column "Company" with the Company values.

The column name is CustomData and is varchar(3000) so it looks like it needs to be converted to XML, unless there is another way to do this.

<?xml version="1.0" encoding="UTF-8"?>
<ArrayOfCustomData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <CustomData name="Company" value="WT001" />
   <CustomData name="Location" value="123456" />
   <CustomData name="DealerCode" value="WT" />
   <CustomData name="Domain" value="WT" />
   <CustomData name="Region" value="1234" />
</ArrayOfCustomData>

Here is the SQL code I'm using.

select CustomData.value('(/ArrayOfCustomData/CustomData name="Company")[1]','varchar(30)') as Company
from tbl_User_Ref

I get this error:

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

Any help is appreciated.

Thanks!

Answer 1

I managed to get it working by using the code below. Not sure why but it looks like my XML is actually UTF-16 and not UTF-8, which might've been giving me problems as well. Thanks for the help.

CAST(CAST(CustomData AS NTEXT) AS XML).value('(/ArrayOfCustomData/CustomData[@name="Company"]/@value)[1]','varchar(3000)') as Company

Answer 2

Your XPath is invalid, try this way instead :

select 
    CustomData.value('(/ArrayOfCustomData/CustomData[@name="Company"]/@value)[1]','varchar(30)') as Company
from tbl_User_Ref

Notice the use of @ at the beginning of attribute name to address an XML attribute, and the use of expression within square-brackets ([....]) to filter XML data (to filter <CustomData> by value of name attribute to be specific).

UPDATE :

If you're using SQL Server you can use CAST() function to convert the column to XML type, for example :

select 
    (CAST(CustomData as xml)).
            value('(/ArrayOfCustomData/CustomData[@name="Company"]/@value)[1]','varchar(30)') as Company
from tbl_User_Ref

SQL Fiddle demo

Another approach using CTE : Select XML from varchar(max) column

Answer 3

DECLARE @DocHandle int

DECLARE @XmlDocument nvarchar(1000)

SET @XmlDocument = N'please put your Query(XML)'

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument

SELECT * FROM OPENXML (@DocHandle, '/ArrayOfCustomData/CustomData',1) WITH (name varchar(10), value varchar(20))

EXEC sp_xml_removedocument @DocHandle