How to select value from xml column in sql table

Question

I have a table as below:

CREATE TABLE [dbo].[testdb](
    [Id] [int] NOT NULL,
    [Description] [nvarchar](4000) NULL,
 CONSTRAINT [PK_testdb] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

And here is value:

INSERT INTO [TempTestDb].[dbo].[testdb]
           ([Id]
           ,[Description])
     VALUES
           (<Id, int,>
           ,'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<retail:customerAttribute xmlns:core="http://www.ctor.com/core" xmlns:hta="http://docs.oasis-open.org/ns/bp4people/ws-humantask/api/200803" xmlns:htd="http://docs.oasis-open.org/ns/bp4people/ws-humantask/200803" xmlns:htt="http://docs.oasis-open.org/ns/bpel4people/ws-humantask/types/200803" xmlns:ns11="http://www.enactor.com/addressLookup/service" xmlns:ns13="http://www.ctor.com/retail/restaurantTableStatus/service" xmlns:ns4="http://www.enactor.com/crm" xmlns:ns5="http://www.ctor.com/retail/storedRetailTransaction/service" xmlns:ns7="http://www.enactor.com/retail/storedRestaurantSaleTransaction/service" xmlns:ns8="http://www.ctor.com/crm/customerLoyalty/service" xmlns:retail="http://www.ctor.com/retail" xmlns:sref="http://docs.oasis-open.org/wsbpel/2.0/serviceref" xmlns:tools="http://www.ctor.com/tools" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <retail:optionPathId>NEAREST_STORE</retail:optionPathId>
    <retail:optionSetId type="customerAttributeSet" optionSetId="NEAREST_STORE">
        <retail:groupId groupHierarchyId="All" groupTypeId="region">All</retail:groupId>
    </retail:optionSetId>
    <retail:dataType>STRING</retail:dataType>
    <retail:lastUpdated>2015-03-13T09:40:59.333Z</retail:lastUpdated>
    <retail:value id="NEAREST_STORE">
        <retail:stringValue>001</retail:stringValue>
    </retail:value>
    <retail:customerId>2600009991693</retail:customerId>
</retail:customerAttribute>
')
GO

I am trying to select the value of 'retail:stringValue'. The query I am trying write is seem to be not working. If anyone can advise me, please do.

My select query:

SELECT TOP 1000 [Id]
      ,[Description].value('(/retail:value/retail:stringValue/node())[1]', 'nvarchar(max)') as test
  FROM [TempTestDb].[dbo].[testdb]

If this is an XML, looks like an XML, quacks like an XML - why **isn't it stored** as `XML` ??? — marc_s, Mar 16 '15 at 11:30

score 1 · Accepted Answer · answered Mar 16 '15 at 11:36

1

Assuming your columns is of datatype XML (as it should be), then you must respect the XML namespace that's involved in your XML to get the value - try this code:

;WITH XMLNAMESPACES(DEFAULT 'http://www.ctor.com/retail')
SELECT 
    [Id],
    [Description].value('(/customerAttribute/value[1]/stringValue)[1]', 'nvarchar(100)') 
FROM 
    [TempTestDb].[dbo].[testdb]

If you look closely at your XML, then you see that most of the nodes are prefixed by a retail: XML namespace prefix:

<retail:customerAttribute

and that prefix is mapped to the XML namespace 'http://www.ctor.com/retail' - so in your select into the XML structure, you must respect that XML namespace and include it in your XQuery - otherwise you get null .....

answered Mar 16 '15 at 11:36

marc_s

732,580
175
1,330
1,459

1

Got this error: Msg 4121, Level 16, State 1, Line 2 Cannot find either column "Description" or the user-defined function or aggregate "Description.value", or the name is ambiguous. – Tun Mar 16 '15 at 11:41
2

I think you should convert VARCHAR to XML first - CAST([Description] AS XML) – Kartic Mar 16 '15 at 11:41
1

@coder: these **XQuery** functions **ONLY** work on `XML` - if you have something else, you must convert it first! – marc_s Mar 16 '15 at 11:42
@marc_s. I've updated the query like below: ;WITH XMLNAMESPACES(DEFAULT 'http://www.ctor.com/retail') SELECT TOP 1000 [Id] ,CAST([Description] as xml).value('(/customerAttribute/value[1]/stringValue)[1]', 'nvarchar(max)') as test FROM [TempTestDb].[dbo].[testdb].. But I'm still getting an error. – Tun Mar 16 '15 at 11:46
As I said : if it looks like XML and quacks like XML - ***it should be*** `XML` ! Fix your table structure - then run the query... – marc_s Mar 16 '15 at 11:47
You have a very unlucky combination - using `NVARCHAR(MAX)` but the XML document has an `encoding="UTF-8"` attribute ... this makes it impossible to directly cast to XML. You'll need to use `CAST(CAST(XmlContent AS VARCHAR(MAX)) AS XML).value(.....)` – marc_s Mar 16 '15 at 11:54
@_marc_s I can't change table structure because the table is owned by external company. – Tun Mar 16 '15 at 11:57

How to select value from xml column in sql table

1 Answers1