none
sql server xml illegal character error

    Question

  • DECLARE

    @XMLData XML

    DECLARE @TextData NVARCHAR(MAX)

    SET @XMLData=(SELECT convert(varchar,ISNULL(O.DateStartOrder,''),101) AS DateStartOrder ,

    O

    .DateStopOrder ,

    O

    .DateDC ,

    OrderDescription

    =

    RTRIM(RTRIM(LTRIM(ISNULL(PH.NamePharm,'') + ' ' ))

    + CASE WHEN ISNULL(ND.NameGeneric,'') = '' THEN ''

    WHEN ISNULL(ND.NameGeneric,'') <> ISNULL(PH.NamePharm,'')

    --THEN ''

    THEN ' (' + ISNULL(ND.NameGeneric,'') + ')'

    ELSE ''

    END ),

    -- + ' ' +

    ISNULL(ND.Strength,'')

    + ' ' + ISNULL(ND.FormDosage,'')

    + ' ' + ISNULL(O.InstructionsDosing,'') AS SIG

    FROM Orders O

    LEFT OUTER JOIN OrderType OT ON O.IdOrderType = OT.IdOrderType

    LEFT OUTER JOIN Pharm PH on PH.IdPharm = O.IdPharm

    LEFT OUTER JOIN NDC ND ON O.IdNDC = ND.IdNDC

    LEFT OUTER JOIN Users U1 WITH (NOLOCK) ON ISNULL(O.IdUserEntered, 0) = U1.IdUser

    AND O.IdClinicNumber = U1.IdClinicNumber

    WHERE O.IdPatient = 1577411

    AND O.IdClinicNumber = 60285

    AND O.IdUserNurse IS NOT NULL

    AND O.DateStartOrder < GETDATE()

    AND O.DATEDC IS NULL

    AND ISNULL(O.DateStopOrder, GETDATE() + 1) > GETDATE()

    AND ISNULL(O.IdSigNotRequired, 0) NOT IN (3, 4)

    AND ISNULL(O.IsRejected,0) = 0

    AND ISNULL(O.IsDisputed,0) = 0

    AND ISNULL(O.IsGeneratedInError,0) = 0

    AND O.IdOrderType = 2

    --ORDER BY OrderDescription

    FOR XML PATH('Results'),ROOT('HomeMedication'))

    select @XMLData

    while running this code i am getting an error

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

    I want

    to cast that to avoid illegal characters. please specify where to use cast statement

    Wednesday, January 02, 2013 10:41 AM

Answers

  • You could throw the data into a temp table and clean it up there.  Somethng like this:

    DECLARE @XMLData XML
    DECLARE @TextData NVARCHAR(MAX)
     
    SELECT convert(varchar,ISNULL(O.DateStartOrder,''),101) AS DateStartOrder , O.DateStopOrder , O.DateDC , OrderDescription 
    = 
    RTRIM(RTRIM(LTRIM(ISNULL(PH.NamePharm,'') + ' ' )) 
    + CASE WHEN ISNULL(ND.NameGeneric,'') = '' THEN '' 
    WHEN ISNULL(ND.NameGeneric,'') <> ISNULL(PH.NamePharm,'')
    --THEN '' 
    THEN ' (' + ISNULL(ND.NameGeneric,'') + ')' 
    ELSE '' 
    END ), 
    -- + ' ' + 
    ISNULL(ND.Strength,'') 
    + ' ' + ISNULL(ND.FormDosage,'') 
    + ' ' + ISNULL(O.InstructionsDosing,'') AS SIG
    INTO #tmp
    FROM Orders O
    	LEFT OUTER JOIN OrderType OT ON O.IdOrderType = OT.IdOrderType 
    	LEFT OUTER JOIN Pharm PH on PH.IdPharm = O.IdPharm 
    	LEFT OUTER JOIN NDC ND ON O.IdNDC = ND.IdNDC 
    	LEFT OUTER JOIN Users U1 WITH (NOLOCK) ON ISNULL(O.IdUserEntered, 0) = U1.IdUser
    		AND O.IdClinicNumber = U1.IdClinicNumber 
    WHERE O.IdPatient = 1577411 
    AND O.IdClinicNumber = 60285 
    AND O.IdUserNurse IS NOT NULL 
    AND O.DateStartOrder < GETDATE() 
    AND O.DATEDC IS NULL 
    AND ISNULL(O.DateStopOrder, GETDATE() + 1) > GETDATE() 
    AND ISNULL(O.IdSigNotRequired, 0) NOT IN (3, 4) 
    AND ISNULL(O.IsRejected,0) = 0 
    AND ISNULL(O.IsDisputed,0) = 0 
    AND ISNULL(O.IsGeneratedInError,0) = 0 
    AND O.IdOrderType = 2
    ORDER BY OrderDescription 
    
    
    -- Cleanup illegal characters
    UPDATE #tmp SET OrderDescription = REPLACE( OrderDescription, CHAR(0x0b), '' )
    
    SET @XMLData  =
    	(
    	SELECT * 
    	FROM #tmp
    	FOR XML PATH('Results'),ROOT('HomeMedication')
    	)
    
    SELECT @XMLData

    • Marked as answer by nareshntr Friday, January 04, 2013 9:40 AM
    Thursday, January 03, 2013 9:59 PM
  • You have some special characters in your column.  0x0b is the hex code for one of those characters, so specifying REPLACE with CHAR(0x0b) means "Replace this character with something else."  If you have other special characters, you will need to write other REPLACE statements.

    For the temp table, add a DROP to the top of the script, eg

    IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

    • Marked as answer by nareshntr Friday, January 04, 2013 9:40 AM
    Friday, January 04, 2013 8:15 AM

All replies

  • You need also the TYPE option in the FOR XML clause.
    Wednesday, January 02, 2013 10:43 AM
  • can you explain how to use and where to use?

    naresh arjala

    Wednesday, January 02, 2013 12:37 PM
  • How many FOR XML clauses do you have? I do only see one..

    FOR XML PATH('Results'),ROOT('HomeMedication')), TYPE


    Take a look at TYPE Directive in FOR XML Queries.
    Wednesday, January 02, 2013 12:42 PM
  • yes only one but it has no impact same error.

    Can we use cast statement for select statement there. I tried but it is compiling with errors


    naresh arjala

    Wednesday, January 02, 2013 12:50 PM
  • Then simplify your test:

    SELECT  CONVERT(VARCHAR, ISNULL(O.DateStartOrder, ''), 101) AS DateStartOrder ,
            O.DateStopOrder ,
            O.DateDC ,
            OrderDescription = RTRIM(RTRIM(LTRIM(ISNULL(PH.NamePharm, '') + ' ')) + CASE WHEN ISNULL(ND.NameGeneric, '') = '' THEN ''
                                                                                         WHEN ISNULL(ND.NameGeneric, '') <> ISNULL(PH.NamePharm, '') --THEN ''
                                                                                              THEN ' (' + ISNULL(ND.NameGeneric, '') + ')'
                                                                                         ELSE ''
                                                                                    END) ,
            ISNULL(ND.Strength, '') + ' ' + ISNULL(ND.FormDosage, '') + ' ' + ISNULL(O.InstructionsDosing, '') AS SIG
    FROM    Orders O
            LEFT OUTER JOIN OrderType OT ON O.IdOrderType = OT.IdOrderType
            LEFT OUTER JOIN Pharm PH ON PH.IdPharm = O.IdPharm
            LEFT OUTER JOIN NDC ND ON O.IdNDC = ND.IdNDC
            LEFT OUTER JOIN Users U1 WITH ( NOLOCK ) ON ISNULL(O.IdUserEntered, 0) = U1.IdUser
                                                        AND O.IdClinicNumber = U1.IdClinicNumber
    WHERE   O.IdPatient = 1577411
            AND O.IdClinicNumber = 60285
            AND O.IdUserNurse IS NOT NULL
            AND O.DateStartOrder < GETDATE()
            AND O.DATEDC IS NULL
            AND ISNULL(O.DateStopOrder, GETDATE() + 1) > GETDATE()
            AND ISNULL(O.IdSigNotRequired, 0) NOT IN ( 3, 4 )
            AND ISNULL(O.IsRejected, 0) = 0
            AND ISNULL(O.IsDisputed, 0) = 0
            AND ISNULL(O.IsGeneratedInError, 0) = 0
            AND O.IdOrderType = 2
    FOR     XML PATH('Results') ,
                ROOT('HomeMedication');

    Wednesday, January 02, 2013 1:29 PM
  • You must have some dodgy characters in your text columns.  Some characters are not allowed in XML ( eg 0x02 ).  You will have to clean these up.  Run the SELECT without the CAST to see what the data looks like and post back.

    Here's a simple example showing how to clean these up:

    DECLARE @t TABLE ( x VARCHAR(20) )
    
    INSERT INTO @t SELECT CHAR(0x02)
    
    --SELECT * FROM @t
    --SELECT x 'y' FROM @t FOR XML PATH	-- this will work
    --SELECT CAST( ( SELECT x 'y' FROM @t FOR XML PATH ) AS XML ) -- this will not work
    
    
    -- Fix it up
    UPDATE @t SET x = REPLACE( x, CHAR(0x02), '?' )
    SELECT 'd', CAST( ( SELECT x 'y' FROM @t FOR XML PATH ) AS XML )	-- these will now work

    • Proposed as answer by Naomi N Wednesday, January 02, 2013 5:00 PM
    Wednesday, January 02, 2013 3:11 PM
  • Sure? These entities are normally correctly encoded:

    DECLARE @t TABLE ( x VARCHAR(20) );
    
    INSERT  INTO @t
            SELECT  CHAR(0x02);
    
    SELECT  *
    FROM    @t
    FOR     XML PATH('Results') ,
                ROOT('HomeMedication');

    returns

    <HomeMedication>
      <Results>
        <x>&#x2;</x>
      </Results>
    </HomeMedication>

    Wednesday, January 02, 2013 4:00 PM
  • Try running my previous script, and uncommenting the commented out lines.  If you just run the normal SELECT FOR XML PATH, the encoded character is generated.  If you then attempt to cast it as XML, the error occurs.
    Wednesday, January 02, 2013 4:23 PM
  • can u plss rewrite my code and copy it here bcoz i am not getting.

    naresh arjala

    Thursday, January 03, 2013 4:50 AM
  • can u plss rewrite my code and copy it here bcoz i am not getting.

    naresh arjala

    Thursday, January 03, 2013 4:50 AM
  • I am still getting the same error.Can you pls rewrite the code for me?

    naresh arjala

    Thursday, January 03, 2013 4:57 AM
  • OrderDescription>Imdur (isosorbide mononitrate&#xB;ext. release&#xB;)</OrderDescription>

     &#xB;

    Due to this special symbol I am getting the error.I need to replace this .Suggest me with what to replace and plss rewrite the code if possible.


    naresh arjala

    Thursday, January 03, 2013 5:00 AM
  • OrderDescription>Imdur (isosorbide mononitrate&#xB;ext. release&#xB;)</OrderDescription>

     &#xB;

    Due to this special symbol I am getting the error.I need to replace this .Suggest me with what to replace and plss rewrite the code if possible.


    naresh arjala


    naresh arjala

    Thursday, January 03, 2013 5:01 AM
  • Sorry, haven't read the commented lines..
    Thursday, January 03, 2013 8:16 AM
  • OrderDescription>Imdur (isosorbide mononitrate&#xB;ext. release&#xB;)</OrderDescription>

     &#xB;

    Due to this special symbol I am getting the error.I need to replace this .Suggest me with what to replace and plss rewrite the code if possible

    Please help me io run the code without errors..


    naresh arjala

    Thursday, January 03, 2013 8:21 AM
  • As suggested, have you tried using REPLACE to clean the special characters? eg

    DECLARE @t TABLE ( x VARCHAR(20) )
    
    INSERT INTO @t SELECT CHAR(0xb)
    
    SELECT * FROM @t
    SELECT x 'y' FROM @t FOR XML PATH	-- this will work
    --SELECT CAST( ( SELECT x 'y' FROM @t FOR XML PATH ) AS XML ) -- this will not work
    
    
    -- Fix it up
    UPDATE @t SET x = REPLACE( x, CHAR(0xb), '?' )
    SELECT CAST( ( SELECT x 'y' FROM @t FOR XML PATH ) AS XML )	-- these will now work
    
    

    Thursday, January 03, 2013 8:46 AM
  • yes but I am trying to use for in insert statement it is not taking.

    Can you rewrite my code to get the desired result without &#xB


    naresh arjala

    Thursday, January 03, 2013 9:05 AM
  • Hi,

    in my example, I show the use of REPLACE to clean out the bad characters.  Can you try that?

    Thursday, January 03, 2013 10:40 AM
  • no actually it is still compiling with errors. Trying to use a double cast statement for the tag where i am getting the error.Plss help me

    cast

    cast(RTRIM(RTRIM(LTRIM(ISNULL(PH.NamePharm,'') + ' ' ))

    + CASE WHEN ISNULL(ND.NameGeneric,'') = '' THEN ''

    WHEN ISNULL(ND.NameGeneric,'') <> ISNULL(PH.NamePharm,'')

    THEN ' (' + ISNULL(ND.NameGeneric,'') + ')'

    ELSE ''

    END )as nvarchar(max))as xml)


    naresh arjala

    Thursday, January 03, 2013 11:00 AM
  • Tried but dont understand how and where to use replace in my code.

    Can you use Replace for my code and copy here?


    naresh arjala

    Thursday, January 03, 2013 11:32 AM
  • You could throw the data into a temp table and clean it up there.  Somethng like this:

    DECLARE @XMLData XML
    DECLARE @TextData NVARCHAR(MAX)
     
    SELECT convert(varchar,ISNULL(O.DateStartOrder,''),101) AS DateStartOrder , O.DateStopOrder , O.DateDC , OrderDescription 
    = 
    RTRIM(RTRIM(LTRIM(ISNULL(PH.NamePharm,'') + ' ' )) 
    + CASE WHEN ISNULL(ND.NameGeneric,'') = '' THEN '' 
    WHEN ISNULL(ND.NameGeneric,'') <> ISNULL(PH.NamePharm,'')
    --THEN '' 
    THEN ' (' + ISNULL(ND.NameGeneric,'') + ')' 
    ELSE '' 
    END ), 
    -- + ' ' + 
    ISNULL(ND.Strength,'') 
    + ' ' + ISNULL(ND.FormDosage,'') 
    + ' ' + ISNULL(O.InstructionsDosing,'') AS SIG
    INTO #tmp
    FROM Orders O
    	LEFT OUTER JOIN OrderType OT ON O.IdOrderType = OT.IdOrderType 
    	LEFT OUTER JOIN Pharm PH on PH.IdPharm = O.IdPharm 
    	LEFT OUTER JOIN NDC ND ON O.IdNDC = ND.IdNDC 
    	LEFT OUTER JOIN Users U1 WITH (NOLOCK) ON ISNULL(O.IdUserEntered, 0) = U1.IdUser
    		AND O.IdClinicNumber = U1.IdClinicNumber 
    WHERE O.IdPatient = 1577411 
    AND O.IdClinicNumber = 60285 
    AND O.IdUserNurse IS NOT NULL 
    AND O.DateStartOrder < GETDATE() 
    AND O.DATEDC IS NULL 
    AND ISNULL(O.DateStopOrder, GETDATE() + 1) > GETDATE() 
    AND ISNULL(O.IdSigNotRequired, 0) NOT IN (3, 4) 
    AND ISNULL(O.IsRejected,0) = 0 
    AND ISNULL(O.IsDisputed,0) = 0 
    AND ISNULL(O.IsGeneratedInError,0) = 0 
    AND O.IdOrderType = 2
    ORDER BY OrderDescription 
    
    
    -- Cleanup illegal characters
    UPDATE #tmp SET OrderDescription = REPLACE( OrderDescription, CHAR(0x0b), '' )
    
    SET @XMLData  =
    	(
    	SELECT * 
    	FROM #tmp
    	FOR XML PATH('Results'),ROOT('HomeMedication')
    	)
    
    SELECT @XMLData

    • Marked as answer by nareshntr Friday, January 04, 2013 9:40 AM
    Thursday, January 03, 2013 9:59 PM
  • Thank you so much Bob. It helped a lot.

    But I have 2 questions for you to answer.

    1. Why do you use '0x0b'.

    2.Problem I am facing with #tmp is that every time I need to give a new name while running.

    Thanks in advance :)


    naresh arjala

    Friday, January 04, 2013 4:49 AM
  • Another solution would be using FOR XML EXPLICIT. Here you can create CDATA sections for element content. See Specifying the CDATA Directive.
    Friday, January 04, 2013 8:06 AM
  • You have some special characters in your column.  0x0b is the hex code for one of those characters, so specifying REPLACE with CHAR(0x0b) means "Replace this character with something else."  If you have other special characters, you will need to write other REPLACE statements.

    For the temp table, add a DROP to the top of the script, eg

    IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

    • Marked as answer by nareshntr Friday, January 04, 2013 9:40 AM
    Friday, January 04, 2013 8:15 AM
  • Thank you so much Bob . Got the end result :)

    naresh arjala

    Monday, January 07, 2013 4:46 AM
  • Is there a way to figure out all the characters that may break text to XML conversion? If, say, we saved some images (binary data) in that column, I guess we may have problems, right?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, January 24, 2013 4:46 PM
  • Yes could be tricky.  I can't think of a particularly easy way; in the past I've just run a cursor through a table, tried to cast the dodgy data to XML inside a TRY CATCH block and trapped for the error.
    Thursday, January 24, 2013 5:01 PM
  • Funny, that's partially what I did. In other words, I put the XML in try and function based solution in catch block.

    The error is

    XML parsing: line 3, character 3, illegal qualified name character

    I am not sure which particular row gave it, but I don't think I need to investigate. BTW, do you think XML solution is faster than scalar UDF to strip tags?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Thursday, January 24, 2013 5:31 PM
    Thursday, January 24, 2013 5:30 PM
  • Well it's best to try it with your data and see.  I wouldn't personally use string methods to operate on XML because you could open yourself up to problems, eg a bug in the function where it doesn't quite work for certain cases.
    Thursday, January 24, 2013 5:43 PM
  • Bob you really helped me for solving that issue.

    Can you solve the other problem I posted

    thread name is "SQL server Dates".

    Plss do check that once.


    naresh arjala

    Friday, January 25, 2013 4:38 AM