Retrieve data from field which stores XML and datatype is CLOB

Question

I want to retrieve the XML value stored in PKDATA data field where name is inpatienttype want returns value 262784091 as inpatienttype

OUTPUT

A	B	Inpatientype
11,212	2587165	262784091

Dataset Image

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns2:DataSet xmlns:ns2="http://www.test.com/t/cn/el">
    <EnumObject>
        <name>InpatientType</name>
        <prompt>InpatientType</prompt>
        <value>262784091</value>
        <radiobutton>false</radiobutton>
    </EnumObject>
    <StringObject>
        <name>xxx</name>
        <prompt></prompt>
        <value>/widget.jsp</value>
        <width>99</width>
    </StringObject>
</ns2:DataSet>

I used the following queries but didn't work

SELECT XMLQUERY(
  '/EnumObject/name'
  PASSING XMLTYPE(e.pkdata)
  RETURNING CONTENT
  ) AS name
FROM EXTDATA as e

---

select x.*
from [dbo].[EXTRADATA] rt
cross join xmltable(
  '/EnumObject/name'
  passing xmltype(rt.packeddata)
  columns name number path 'name/@value'
) x

Getting this error

Parse error at line: 3, column: 3: Incorrect syntax near 'PASSING'.

Can someone please help me here

Answer 1

In Microsoft SQL Server you can use XQuery via the nodes(), query() and value() functions.

If the source data involves custom XML namespaces you can also use with xmlnamespaces to namespace qualify the nodes of your XQuery, for example:

with xmlnamespaces (
  'http://www.test.com/t/cn/el' as cnel
)
select
  EnumObject.value('(./name/text())[1]', 'varchar(50)') as name,
  EnumObject.value('(./prompt/text())[1]', 'varchar(50)') as prompt,
  EnumObject.value('(./value/text())[1]', 'varchar(50)') as value
from dbo.TOCEXTRADATA
cross apply packeddata.nodes('/cnel:DataSet/EnumObject') p(EnumObject);

name	prompt	value
InpatientType	InpatientType	262784091

The following query avoids the custom namespace on the root node and returns an equivalent result:

select
  EnumObject.value('(./name/text())[1]', 'varchar(50)') as name,
  EnumObject.value('(./prompt/text())[1]', 'varchar(50)') as prompt,
  EnumObject.value('(./value/text())[1]', 'varchar(50)') as value
from dbo.TOCEXTRADATA
cross apply packeddata.nodes('//EnumObject') p(EnumObject);

name	prompt	value
InpatientType	InpatientType	262784091

---

Edit: when the data type of the packeddata column isn't xml you'll need to cast it to the correct type. The two example queries above need to be modified in the following way:

with xmlnamespaces (
  'http://www.test.com/t/cn/el' as cnel
)
select
  EnumObject.value('(./name/text())[1]', 'varchar(50)') as name,
  EnumObject.value('(./prompt/text())[1]', 'varchar(50)') as prompt,
  EnumObject.value('(./value/text())[1]', 'varchar(50)') as value
from dbo.TOCEXTRADATA
outer apply ( select cast(packeddata as xml) as xmlData ) oa
cross apply xmlData.nodes('/cnel:DataSet/EnumObject') p(EnumObject);

select
  EnumObject.value('(./name/text())[1]', 'varchar(50)') as name,
  EnumObject.value('(./prompt/text())[1]', 'varchar(50)') as prompt,
  EnumObject.value('(./value/text())[1]', 'varchar(50)') as value
from dbo.TOCEXTRADATA
outer apply ( select cast(packeddata as xml) as xmlData ) oa
cross apply xmlData.nodes('//EnumObject') p(EnumObject);