CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2012
Nikhil
Nikhil

Reputation: 31

Return xml data from SQL query

I have a SQL query. 

SELECT convert(xml, A.[business_line]).value('(/collection/object/fields/field/value)[1]', 'varchar(3)')
from [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS] A

I am getting an error while running this query.

Error states

Msg 9413, Level 16, State 1, Line 1
XML parsing: line 1, character 30, A string literal was expected

A.business_line has 2 set of values.

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

2) <collection><object parentid="ce57cc75-3966-478f-bf25-5e3abf716f96" parenttype="Object"><fields/></object></collection>

Can anyone help me on this?

Upvotes: 1

Views: 230

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

use try_convert() instead. but if you have sql server 2012 up.

SELECT try_convert(xml, A.[business_line]).value('(/collection/object/fields/field/value)[1]', 'varchar(3)')
from [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS] A

applying on your original query.

select try_convert(xml, col).value('(/collection/object/fields/field/value)[1]', 'varchar(3)') 
from (
    select col= Coalesce(replace(replace(A.[business_line], char(10), ''''), char(13), ''''),'''') 
    from [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS] A) t1

Upvotes: 1

Alan Burstein
Alan Burstein

Reputation: 7918

There's one or more rows that have a bad character in the XML.

Run this to confirm:

SELECT convert(xml, A.[business_line])
FROM [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS] A;

If you still see an error you can track down the bad rows using TRY_CONVERT or TRY_CAST like this.

SELECT TRY_CONVERT(xml, A.[business_line])
FROM [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS] A
WHERE TRY_CONVERT(xml, A.[business_line]) IS NULL;

Upvotes: 1

Related Questions