Why do I get error "illegal qualified name character" when trying to INSERT

Question

Can someone help me get the following INSERT Statement working. I always get the following error when executing the below code:

Msg 9455, Level 16, State 1, Line 3 XML parsing: line 1, character 18, illegal qualified name character

Thanks so much in advance!

--CREATE DATABASE TestDB;
--GO 

USE TestDB;
GO


IF OBJECT_ID ('AllDataTypes','U') IS NOT NULL
    DROP TABLE AllDataTypes;
GO


CREATE TABLE AllDataTypes
( 
    --Character data types
    [char] char(3),
    [varchar] varchar(10),
    [varcharMAX] varchar(MAX),
    [text] text,

    --Unicode character data types
    [nchar] nchar(3),
    [nvarchar] nvarchar(10),
    [nvarcharMAX] nvarchar(MAX),
    [ntext] ntext,

    --Binary data types
    [bit] bit,
    [binary] binary(3),
    [varbinary] varbinary(10),
    [varbinaryMAX] varbinary(MAX),
    [image] image,


    --Numeric data types (exact)
    [tinyint] tinyint,
    [smallint] smallint,
    [int] int,
    [bigint] bigint,
    [decimal] decimal(14,6),
    [numeric] numeric(14,6),
    [smallmoney] smallmoney,
    [money] money,

--Numeric data types (approx.)
    [float] float,
    [real] real,


     --Date data types
    [datetime] datetime,
    [datetime2] datetime2,
    [smalldatetime] smalldatetime,
    [date] date,
    [time] time,
    [datetimeoffset] datetimeoffset,
    [timestamp] timestamp,

 --Special data types
    [sql_variant] sql_variant,
        [uniqueidentifier] uniqueidentifier,
    [xml] xml,
    [hierarchyid] hierarchyid,

     --Spatial data types
    [geometry] geometry,
    [geography] geography  

);
GO


INSERT INTO [AllDataTypes]
(
         [char]
       ,[varchar]
       ,[varcharMAX]
       ,[text]
       ,[nchar]
       ,[nvarchar]
       ,[nvarcharMAX]
       ,[ntext]
       ,[bit]
       ,[binary]
       ,[varbinary]
       ,[varbinaryMAX]
       ,[image]
       ,[tinyint]
       ,[smallint]
       ,[int]
       ,[bigint]
       ,[decimal]
       ,[numeric]
       ,[smallmoney]
       ,[money]
       ,[float]
       ,[real]
       ,[datetime]
       ,[datetime2]
       ,[smalldatetime]
       ,[date]
       ,[time]
       ,[datetimeoffset]
       ,[sql_variant]
       ,[uniqueidentifier]
       ,[xml]
       ,[hierarchyid]
       ,[geometry]
       ,[geography])
 VALUES
(
         -- Character data
          'ABC'              --  <char, char(3),>
           ,'Varying'            -- ,<varchar, varchar(10),>
           ,'MAX of over 1 billion chars (varying)'-- ,<varcharMAX, varchar(max),>
           ,'Up to 2 GB of data!'    -- ,<text, text,>

         --Unicode character data
           ,N'Úni'               -- ,<nchar, nchar(3),>
           ,N'Únicode'           -- ,<nvarchar, nvarchar(10),>
           ,N'Pési fór á Þingvallaheiði' -- ,<nvarcharMAX, nvarchar(max),>
           ,N'Íslenska - ÓÍÁÚÞÐÐÆÖ'  -- ,<ntext, ntext,>

         --Binary data
         ,0-- ,<bit, bit,>
           ,CAST('ABC' as binary(3))-- ,<binary, binary(3),>
           ,CAST('Varying' as varbinary(10))-- ,<varbinary, varbinary(10),>
           ,CAST('MAX of 2 GB binary data (varying)' as varbinary(MAX)) -- ,<varbinaryMAX,     varbinary(MAX),> 
           ,(select * from OPENROWSET(BULK 'C:\Users\adminstrator\Documents\SQL Server     Management Studio\image.jpg', SINGLE_BLOB) i) -- ,<image, image,>

         --Numeric data (exact)
           ,255              -- ,<tinyint, tinyint,>
           ,32767                -- ,<smallint, smallint,>
           ,2147483647           -- ,<int, int,>
           ,9223372036854775807  -- ,<bigint, bigint,>
           ,99999999.999999      -- ,<decimal, decimal(14,6),>
           ,99999999.999999      -- ,<numeric, numeric(14,6),>
           ,214748.3647          -- ,<smallmoney, smallmoney,>
           ,922337203685477.5807     -- ,<money, money,>

         --Numeric data (approx.)
           ,1.79E+308            -- ,<float, float,>
         ,3.40E+38           -- ,<real, real,>

         --DateTime data
         ,GETDATE()                       -- ,<datetime, datetime,>
           ,SYSDATETIME()                         -- ,<datetime2, datetime2(7),>
           ,CAST(GETDATE() as smalldatetime)          -- ,<smalldatetime, smalldatetime,>
           ,CAST(GETDATE() as date)           -- ,<date, date,>
           ,CONVERT(varchar(12), GETDATE(), 14)   -- ,<time, time(7),>
           ,TODATETIMEOFFSET(GETDATE(), '-05:00')     -- ,<datetimeoffset, datetimeoffset(7),>


         --Special data
           ,GETDATE()                         -- ,<sql_variant, sql_variant,>
         ,NEWID()                             -- ,<uniqueidentifier, uniqueidentifier,>
           ,'<XMLRoot>-- ,<xml, xml,>
             <Person>
                <FirstName>Heimir</FirstName>
                <LastName>Jonsson</LastName>
             </Person>
          </XMLRoot>'                         
           ,null                              -- ,<hierarchyid, hierarchyid,>

         --Spatial data
           ,geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)    -- ,    <geometry, geometry,>
           ,geography::STMPolyFromText('MULTIPOLYGON(((-122.358 47.653, -122.348 47.649,     -122.358 47.658, -122.358 47.653)), ((-122.341 47.656, -122.341 47.661, -122.351 47.661, -122.341     47.656)))', 4326)       -- ,<geography, geography,>
);
GO


SELECT 
    * 
FROM [AllDataTypes] ;
GO

Patrick Hofman Patrick Hofman · Accepted Answer · 2014-12-04T11:30:36

Because your XML format is invalid:

,'<XMLRoot>-- ,<xml, xml,><!-- remove this invalid syntax -->
 <Person>
    <FirstName>Heimir</FirstName>
    <LastName>Jonsson</LastName>
 </Person>
</XMLRoot>'

Should be:

,'<XMLRoot>
 <Person>
    <FirstName>Heimir</FirstName>
    <LastName>Jonsson</LastName>
 </Person>
</XMLRoot>'

Why do I get error "illegal qualified name character" when trying to INSERT

1 Answers