Home / Questions / 462573
Accepted
John Doe
Asked: 2022-06-17 13:57:14 +0800 CST 2022-06-17 13:57:14 +0800 CST

Why my query returns question marks

  • 772

I am getting question marks as a result of reading a column to which I am reading the xml

The query with which I am obtaining the records and in which I obtain the question mark in one of them is the following:

Select tp_ColumnSet.value('(/nvarchar1)[1]', 'nvarchar(100)') Titulo,
    tp_ColumnSet.value('(/nvarchar3)[1]', 'nvarchar(100)') Prioridad,
    tp_ColumnSet.value('(/nvarchar4)[1]', 'nvarchar(100)') Estado,
    tp_ColumnSet.value('(/int3)[1]', 'int') Asignado,
    tp_ColumnSet.value('(/int2)[1]', 'int') TipoSoporte,
    tp_ColumnSet.value('(/int5)[1]', 'int') ProblemaReal,
    Convert(numeric(15,2), tp_ColumnSet.value('(/float1)[1]', 'float')) HrsReales,
    tp_ColumnSet.value('(/nvarchar10)[1]', 'varchar(MAX)')  AsignadoSG,
    tp_ColumnSet.value('(/ntext2)[1]', 'varchar(MAX)') Descripcion
From Content.dbo.UserData   
Where ListId = 'C4B4' and tp_ColumnSet.value('(/nvarchar1)[1]', 'nvarchar(100)') = 'TICKET DE PRUEBA'

The result of the query is as follows:

+------------------------------------------------------------------+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------
|    Titulo        | Prioridad | Estado |  Asignado |  TipoSoporte | ProblemaReal |  HrsReales |  AsignadoSG  |                                                                      Descripcion  
+----+------------------+--------------------+-----------+---------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------
| TICKET DE PRUEBA |  (3) Baja |Iniciada|   497     |      2       |     31       |    12.00   |   ????????   |  <div class="ExternalClass9DB19E6929B74D6EB9D92825BBE42503"><p>​ESTA ES UNA PRUEBA<br></p></div>    
+------+---------+------------+-------------------+-------------+-------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------

As you can see, what the column returns AsignadoSGare question marks ????????when the XML field contains the following:

<float1>1.200000000000000e+001</float1>
<int2>2</int2>
<int3>497</int3>
<int5>31</int5>
<ntext2>&lt;div class="ExternalClass9DB19E6929B74D6EB9D92825BBE42503"&gt;&lt;p&gt;​ESTA ES UNA PRUEBA&lt;br&gt;&lt;/p&gt;&lt;/div&gt;</ntext2>
<nvarchar1>TICKET DE PRUEBA</nvarchar1>
<nvarchar3>(3) Baja</nvarchar3>
<nvarchar4>Iniciada</nvarchar4>
<nvarchar10>쐘ﾯ虳䍵᮴됱볈违</nvarchar10>

As can be seen in the xml, the label <nvarchar10>contains strange characters, which is not right either, and when returning the query, it returns question marks.

Analyzing a little I can believe that it is a type collation problem collatebut I do not have much knowledge in them, or if someone has any other hypothesis, it is welcome.

sql-server

1 Answers

  1. Best Answer

    The XML you are working with has Unicode<nvarchar10> characters in the element , which take up more space than UTF-8 or UTF-16 characters .

    The second parameter of valueis the type that SQL Server has to cast the result to, and you're telling it to cast to varchar(MAX). However, since varchar it only supports UTF-8 or UTF-16 (as of SQL Server 2019 if the collation is UTF-8, it supports unicode as well, but I guess that's not your case from the output you're seeing), you can't process the content of your XML.

    The solution is to change the second parameter so that it nvarchar(MAX) does support Unicode . So the corrected query would be:

    Select tp_ColumnSet.value('(/nvarchar1)[1]', 'nvarchar(100)') Titulo,
    tp_ColumnSet.value('(/nvarchar3)[1]', 'nvarchar(100)') Prioridad,
    tp_ColumnSet.value('(/nvarchar4)[1]', 'nvarchar(100)') Estado,
    tp_ColumnSet.value('(/int3)[1]', 'int') Asignado,
    tp_ColumnSet.value('(/int2)[1]', 'int') TipoSoporte,
    tp_ColumnSet.value('(/int5)[1]', 'int') ProblemaReal,
    Convert(numeric(15,2), tp_ColumnSet.value('(/float1)[1]', 'float')) HrsReales,
    tp_ColumnSet.value('(/nvarchar10)[1]', 'nvarchar(MAX)')  AsignadoSG,
    tp_ColumnSet.value('(/ntext2)[1]', 'varchar(MAX)') Descripcion
From Content.dbo.UserData   
Where ListId = 'C4B4' and tp_ColumnSet.value('(/nvarchar1)[1]', 'nvarchar(100)') = 'TICKET DE PRUEBA'
    • 4