CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlsql-server-2012
Nikhil
Nikhil

Reputation: 31

Query value from XML data element

I have a SQL query which returns data from a column. However the data in the column is in XML. I need value inside value element. How can I do so?

Please see my below SQL query.

SELECT 
    A.[business_line]
FROM
    [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS] A
INNER JOIN
    [EU_OTH_REG].[dbo].[TBL_EU_OTH_REG_MST_LOOKUP] B ON B.code = A.product_substance_type

The output to this query is

<collection><object parentid="ce57cc75-3966-478f-bf25-5e3abf716f96" parenttype="Object"><fields><field name="code"><value>BL2</value></field></fields></object></collection>

I want the value BL2 from this. The data is inside the value element. Can anyone help how to do so? I tried using stuff but I wasn't able to get the value.

Upvotes: 0

Views: 79

Answers (1)

Dale K
Dale K

Reputation: 27226

XML.value() does the trick as follows:

declare @Test xml = '<collection><object parentid="ce57cc75-3966-478f-bf25-5e3abf716f96" parenttype="Object"><fields><field name="code"><value>BL2</value></field></fields></object></collection>';

select @Test.value('(/collection/object/fields/field/value)[1]', 'varchar(3)' )

Returns:

BL2

So add that to your query:

select A.[business_line].value('(/collection/object/fields/field/value)[1]', 'varchar(3)'
from [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS] A
inner join [EU_OTH_REG].[dbo].[TBL_EU_OTH_REG_MST_LOOKUP] B
    on B.code = A.product_substance_type

As you have indicated that A.[business_line] is not of XML datatype, just convert it before calling value:

convert(xml, A.[business_line]).value

Note: I have assumed that its only ever a 3 character result i.e. varchar(3) but if not adjust to suit.

Upvotes: 2

Related Questions