Unable to run an XMLQuery on a XML statement

Question

I have a problem with XML queries in SQL Server. I usually run a query on a @XML variable to fetch data like this but in a certain statement it does not work.

This code works correctly

Declare @XML as XML = 
'
<ResponseData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Remain>12345654321.1234</Remain>
  <ErrorMsg />
</ResponseData>';
;

Select 
    F.value('Remain[1]','decimal(18,4)') as A,
    F.value('ErrorMsg[1]','varchar(100)') as B
FROM  @XML.nodes('ResponseData')Tbl(F);

but if I add a new row in the first of @XML it can0t parse and return following error

XML parsing: line 2, character 6, text/xmldecl not at the beginning of input

Declare @XML as XML = 
'
<?xml version="1.0" encoding="utf-16"?>
<ResponseData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Remain>12345654321.1234</Remain>
  <ErrorMsg />
</ResponseData>';
;

Select 
    F.value('Remain[1]','decimal(18,4)') as A,
    F.value('ErrorMsg[1]','varchar(100)') as B
FROM  @XML.nodes('ResponseData')Tbl(F);

The <?xml version="1.0" encoding="utf-16"?> added to the @XML variable.

thanks

podiluska · Accepted Answer · 2013-10-21 11:17:21Z

2

There can't be any characters between the start of the string and the <?xml

Just remove the line break.

If you want to use utf-16, you'll need to prefix the string with an N

Declare @XML as XML = N'<?xml version="1.0" encoding="utf-16"?>....

answered Oct 21, 2013 at 11:17

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

1 Comment

naser daneshi Over a year ago

I created an SSIS package that fetch data from a web service task. It fills data into a text file in my disk so I can't modify the statement. Is there another solution?

Collectives™ on Stack Overflow

Unable to run an XMLQuery on a XML statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related