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
– marc_s, Commented Mar 16, 2015 at 11:30

marc_s · Accepted Answer · 2015-03-16 11:36:07Z

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, 2015 at 11:36

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Tun Over a year ago

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.

Kartic Over a year ago

I think you should convert VARCHAR to XML first - CAST([Description] AS XML)

marc_s Over a year ago

@coder: these XQuery functions ONLY work on XML - if you have something else, you must convert it first!

Tun Over a year ago

@marc_s. I've updated the query like below: ;WITH XMLNAMESPACES(DEFAULT '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.

marc_s Over a year ago

As I said : if it looks like XML and quacks like XML - it should be XML ! Fix your table structure - then run the query...

|

Collectives™ on Stack Overflow

How to select value from xml column in sql table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related