Return xml data from SQL query

Question

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?

As per my comments on your other question, you'll have to check every value in A.[business_line], depending on how many rows you have you may be able to do that manually using an online XML validator, or you may need to find some automated way to do it. Unfortunately no one will be able to tell you what is wrong without seeing the data. — Dale K
– Dale K, Commented Oct 9, 2019 at 4:49
If you use the manual test code from my other answer i.e. declare @Test... to test both of those, what do you get? — Dale K
– Dale K, Commented Oct 9, 2019 at 4:56
If I do that it gives me correct value. For 1st data it provide BL2 and for 2nd data it provides' NULL' @DaleBurrell — Nikhil
– Nikhil, Commented Oct 9, 2019 at 5:00
Exactly, so the data you have shown isn't identical to whats actually in your database. — Dale K
– Dale K, Commented Oct 9, 2019 at 5:02

Ed Bangga · Accepted Answer · 2019-10-09 05:33:06Z

1

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

edited Oct 9, 2019 at 5:33

answered Oct 9, 2019 at 5:17

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

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

6 Comments

Nikhil Over a year ago

A last help needed. When I append it into a query. It is showing me error in syntax. Please see my below query.

Nikhil Over a year ago

Declare @Query as nvarchar(1000) Set @Query='set nocount on; Select Coalesce(replace(replace(A.[type], char(10), ''''), char(13), ''''),'''') as Type, 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'

Nikhil Over a year ago

@metal Can you assist if I am doing something wrong here?

Nikhil Over a year ago

Error I am getting is ---- Msg 102, Level 15, State 1, Line 6 Incorrect syntax near '/'.

Ed Bangga Over a year ago

@NikhilKotian, please create separate for this issue.

|

Alan Burstein · Accepted Answer · 2019-10-09 05:25:34Z

1

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;

answered Oct 9, 2019 at 5:25

Alan Burstein

8,0081 gold badge18 silver badges19 bronze badges

Collectives™ on Stack Overflow

Return xml data from SQL query

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related