XML parsing: line 2, character 1, illegal xml character

Question

Hi all,

I am new to Xqueries in T-SQL.

I have an Xquery that I am debuggung but I end up getting an error.

Below is my code.

use cg2
select top 10
PatID ,
cast(Convert(varchar,BeginDate,101) as datetime2) as V_DATE,
Convert(varchar,BeginDate,108) as V_TIME,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Treatment Status"]/value/property[@desc="status"]/@value)[1]','varchar(50)'),'0')      as TREAT_STAT_CD,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Height"]/value/@value)[1]','varchar(50)'),'.000')                                      as HEIGHT_CM,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Weight"]/value/@value)[1]','varchar(50)'),'.000')                                      as WEIGHT_KG,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Head Circ"]/value/@value)[1]','varchar(50)'),'.000')                                   as HEAD_CIRCMF_CM,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Temp"]/value/@value)[1]','varchar(50)'),'0.00')                                        as TEMP_DEG_C,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Temp"]/value/property[@desc="method"]/@code)[1]','varchar(50)'),'0')                   as TEMP_METHOD_CD,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Resp"]/value/@value)[1]','varchar(50)'),'0')                                           as RESP_RATE,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Peak Flow"]/value/@value)[1]','varchar(50)'),' ')                                      as PeakFlow,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Pulse Ox"]/value/@value)[1]','varchar(50)'),' ')                                       as PulseOxy,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Pulse Ox"]/value/property[@desc="delivery"]/@value)[1]','varchar(50)'),' ')            as PulseOxyDelivery,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Pulse Ox"]/value/property[@desc="oxdetail"]/@value)[1]','varchar(50)'),' ')            as PulseOxyDetailPerMinute,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Pulse Ox"]/value/property[@desc="oxhumidity"]/@value)[1]','varchar(50)'),' ')          as PulseOxyHumidityFlag,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="BP"]/value/translation[@desc="systolic"]/@value)[1]','varchar(50)'),'0')               as BP_SYS_MM,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="BP"]/value/translation[@desc="diastolic"]/@value)[1]','varchar(50)'),'0')              as BP_DIA_MM,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="BP"]/value/property[@desc="location"]/@value)[1]','varchar(50)'),'0')                  as BP_LOC_CD,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="BP"]/value/property[@desc="position"]/@value)[1]','varchar(50)'),'0')                  as BP_POS_CED,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="BP"]/value/property[@desc="cuff size"]/@value)[1]','varchar(50)'),'0')                 as BP_CUFFSIZE_CD,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Pulse"]/value/@value)[1]','varchar(50)'),'0')                                          as PULSE_RATE,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Pulse"]/value/property[@desc="method"]/@value)[1]','varchar(50)'),'0')                 as PULSE_METHOD_CD,
ISNULL(CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Pulse"]/value/property[@desc="rhythm"]/@value)[1]','varchar(50)'),'0')                 as PULSE_RHYTHM_CD,
' ' AS GRP_TP_CD
from      xmltable (nolock)
left join Orders (nolock)
on xmltable.OrderNo = Orders.OrderNo
where Orders.StatusSCV = 1
and ObservationTypeCodeCV = 'Vitals'
and CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Height"]/value/@value)[1]','varchar(50)') is not null
and CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Pulse Ox"]/value/@value)[1]','varchar(50)') <> 0
and CAST(XMLResultText as XML).value ('(/vitals/entry/observation[code/@desc="Pulse Ox"]/value/property[@desc="delivery"]/@value)[1]','varchar(50)') <> 0 

This is the error I am getting.

Msg 9420, Level 16, State 1, Line 3

XML parsing: line 2, character 1, illegal xml character

Does this error has anything to do with the bad data ?

Thanks,

Ion

Answer 1

Yep it's bad data.  Can you post a small example of this XML which looks like it is held in a character column.  It may also be the encoding ( eg UTF-8 ).

Have you considered storing it using the XML datatype?

Answer 2

Consider using something like the following:

DECLARE @Xml XML;
SET @Xml = '<ProductCategory>
  <ProductCategoryID>1</ProductCategoryID>
  <Name>Bikes</Name>
  <Codes>
	<Code Id="1" Name="Tester1"/>
	<Code Id="2" Name="Tester2"/>
  </Codes>
  <ModifiedDate>2002-06-01T00:00:00</ModifiedDate>
</ProductCategory>
<ProductCategory>
  <ProductCategoryID>2</ProductCategoryID>
  <Name>Components</Name>
  <Codes>
	<Code Id="3" Name="Tester3"/>
	<Code Id="2" Name="Tester2"/>
  </Codes>
  <ModifiedDate>2002-05-01T00:00:00</ModifiedDate>
</ProductCategory>
<ProductCategory>
  <ProductCategoryID>3</ProductCategoryID>
  <Name>Clothing</Name>
    <Codes>
	<Code Id="1" Name="Tester1"/>
	<Code Id="2" Name="Tester2"/>
  </Codes>
  <ModifiedDate>2002-06-23T00:00:00</ModifiedDate>
</ProductCategory>
<ProductCategory>
  <ProductCategoryID>4</ProductCategoryID>
  <Name>Accessories</Name>
  <Codes>
	<Code Id="5" Name="Tester5"/>
	<Code Id="3" Name="Tester3"/>
  </Codes>
  <ModifiedDate>2002-06-11T00:00:00</ModifiedDate>
</ProductCategory>';
SELECT  [ProductCategoryID] = myParsedXml.Entity.value('ProductCategoryID[1]', 'int') ,--get the value of an element,
		[CodeXML] = myParsedXml.Entity.query('Codes/Code[@Id]'),
		[CodeId] = myParsedNode.NodeEntity.value('@Id', 'Int'),
		[CodeName] = myParsedNode.NodeEntity.value('@Name', 'nvarchar(100)'),
		[BikeName] = myParsedXml.Entity.value('Name[1]', 'nvarchar(100)'),
		[ModifiedDate] = CAST(myParsedXml.Entity.value('ModifiedDate[1]', 'nvarchar(100)') AS DATE)
FROM    @Xml.nodes('//ProductCategory') AS myParsedXml(Entity)
CROSS APPLY Entity.nodes('Codes/Code') AS myParsedNode(NodeEntity)

It results in something like this

It's an example I made, so you will have to put your Xml in this spot and change the names.

Success!

---

Jurgen Asselman - MCITP SQL Server 2008 - Database Developer

Answer 3

Hello,

Attached is the one of the rows I have i the XMLRes field.

<vitals inError="false" dateTime="04/05/2011 00:00:00" migrated="true">
<entry>
<observation version="1">
<observer userno="1111vx" associateno="1111Y" changeSet="1" />
<code code="Weight" desc="Weight" />
<statusCode code="completed" />
<entryTime value="04/05/2011 00:00:00" />
<recordedTime value="4/5/2011 7:31:42 PM" />
<value unit="kg" value="14.33"><translation unit="lbs" value="31.61" />
<property desc="method" value="actual" /></value>
<comments />
</observation></entry><entry><observation version="1"><observer userno="1111x" associateno="00rty" changeSet="1" /><code code="Temp" desc="Temp" /><statusCode code="completed" /><entryTime value="04/05/2011 19:31:10" /><recordedTime value="4/5/2011 7:31:42 PM" /><value unit="C" value="36.2"><translation unit="MF" value="00.0" /><property desc="method" code="5" value="Tympanic" /></value><comments /></observation></entry><commentHistory><comment userno="XXX11" associateno="94082" datetime="4/5/2011 7:31:07 PM" recordedTime="4/5/2011 7:31:42 PM" changeSet="1">Temp:Pre-sALE;</comment></commentHistory></vitals>

I have over millions of records in this field with almost the same  XML Schema. How can I reference all these millions of records in an XML variable?

Thanks,

Ion

---

Ion

Answer 4

That's valid XML so is unlikely to be causing your error.  You'll need to find the record with the invalid XML and post it.  If you can't do it by visual inspection, you could run a cursor through the table, try the cast to xml, see what fails.

Let us know if you need help with scripting that.

Answer 5

Do you have any sample code on how to come up with a cursor?

---

Ion

Answer 6

OK try this.  The idea is copy your text XML over to a temp table and attempt to cast it.  If it succeeds fine.  If not record the error message and carry on.  You may need to alter the script slightly to add in a row identifier from your table.

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
CREATE TABLE #tmp ( originalRowId INT PRIMARY KEY, XMLResultText NVARCHAR(MAX), XMLResultXML XML, errorMessage NVARCHAR(2048) )
GO

-- Copy original data over to temp table for checking
INSERT INTO #tmp ( originalRowId, XMLResultText )
SELECT 
	rowId,	-- your original row id to track where the record came from
	XMLResultText
FROM XMLTable
GO


DECLARE @originalRowId INT

DECLARE update_cursor CURSOR LOCAL FOR 
SELECT originalRowId
FROM #tmp
FOR UPDATE OF XMLResultXML

OPEN update_cursor

FETCH NEXT FROM update_cursor INTO @originalRowId
WHILE @@FETCH_STATUS = 0
BEGIN

	BEGIN TRY
	
		-- Try and cast to XML
		UPDATE #tmp
		SET XMLResultXML = CAST( XMLResultText AS XML )
		WHERE CURRENT OF update_cursor
	
	END TRY
	BEGIN CATCH
		
		RAISERROR( 'Error with rowId %i.', 10, 1, @originalRowId )

		UPDATE #tmp
		SET errorMessage = ERROR_MESSAGE()
		WHERE CURRENT OF update_cursor

	END CATCH
	
	FETCH NEXT FROM update_cursor INTO @originalRowId
END

CLOSE update_cursor
DEALLOCATE update_cursor
GO

-- Show results
SELECT *
FROM #tmp
WHERE errorMessage IS NOT NULL

Answer 7

Bob,

My cursor is running now, I will let you know my findings.

Thanks,

Ion

---

Ion

Answer 8

Ha it could take a while if you really do have millions of rows of XML and you might need some diskspace too...

Good luck!

Answer 9

Hey,

Attached is one of XMLResultText field rows retrieved from the Temp table where there are errors.

<?xml version="1.0" encoding="utf-8"?><vitals inError="false" dateTime="06/23/2006 00:00:00" migrated="true"><entry><observation version="1"><observer userno="XXXX" associateno="YYYYY" changeSet="1" /><code code="Height" desc="Height" /><statusCode code="completed" /><entryTime value="06/23/2006 00:00:00" /><recordedTime value="2006-06-23 10:46:08" /><value unit="cm" value="163.3"><translation unit="in" value="64.3" /><property desc="method" value="actual" /></value><comments /></observation></entry><entry><observation version="1"><observer userno="15138" associateno="YYYYY" changeSet="1" /><code code="Weight" desc="Weight" /><statusCode code="completed" /><entryTime value="06/23/2006 00:00:00" /><recordedTime value="2006-06-23 10:46:08" /><value unit="kg" value="52.62"><translation unit="lbs" value="116.02" /><property desc="method" value="actual" /></value><comments /></observation></entry><entry><observation version="1"><observer userno="XXXX" associateno="YYYYY" changeSet="1" /><code code="Temp" desc="Temp" /><statusCode code="completed" /><entryTime value="06/23/2006 00:00:00" /><recordedTime value="2006-06-23 10:46:08" /><value unit="C" value="37.3"><translation unit="GENDER" value="99.2" /><property desc="method" code="0" value="" /></value><comments /></observation></entry><commentHistory><comment userno="XXXXX" associateno="YYYYY" datetime="6/23/2006 10:46:02 AM" recordedTime="6/23/2006 10:46:08 AM" changeSet="1" /></commentHistory></vitals>

Thanks,

Ion.

---

Ion

Answer 10

The error I get for that is 'unable to switch the encoding'.  The easiest thing to do is get rid of it as it's not required, eg

UPDATE #tmp
SET XMLResultXML = CAST( REPLACE( XMLResultText, '<?xml version="1.0" encoding="utf-8"?>', '' ) AS XML )

Answer 11

Hey Bob,

Thank you so much for your help.

Problem solved.

Thanks and Regards,

Ion.

---

Ion