Geek asks and answers

Convert string to xml and embed Sql Server

We have a SQL Server 2008 R2 database table with XML stored in a type column VARCHAR.

Now I need to get some of the xml elements.

So, I want to first convert the xml saved as a data type VARCHARto xml saved as a data type xml.

Example:

Table a

Id(int) , ProductXML (varchar(max))

Table B

Id(int), ProductXML(XML)

I want to convert ProductXMLfrom Table Ato an XML data type and paste in Table B.

I tried using functions CAST()and CONVERT()as below:

insert into TableB (ProductXML)
select CAST(ProductXML as XML) from TableA;

Converted similarly, but I get an error

XML parsing: unable to switch encoding

Is there any way to convert records VARCHARinto a table into XML records?

XML: , .

. XML , xml .

+9
3

XML, :

CONVERT(XML, '<root><child/></root>')
CONVERT(XML, '<root>          <child/>         </root>', 1)
CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

nvarchar varbinary ( Microsoft):

SQL Server, [n] [var] char, [n] , varbinary , xml (CAST) ( CONVERT) xml. Untyped XML , , . , xml, . . XML- Untyped XML.

XML- (, UTF-8, UTF-16, windows-1252). , XML- .

nvarchar Unicode, ​​ UTF-16 UCS-2, XML XML- , Unicode. , XML- Unicode, . XML- UTF-16 UTF-16 (BOM), , , Unicode .

varchar XML-/ XML. varchar , , XML. XML , , .

varbinary , XML. , XML- . . , XML- UTF-16 UTF-16, UTF-8.

XML- , XML , XML, varbinary. , XML OpenRowset() , varbinary (max):

select CAST(x as XML) 
from OpenRowset(BULK 'filename.xml', SINGLE_BLOB) R(x)

SQL Server XML , UTF-16. , , , .

:

CONVERT(XML, CONVERT(NVARCHAR(max), ProductXML))
+22

:

select CAST(REPLACE(CAST(column3 AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML) from table
+3

(. ). SQL. - objectName, ObjectType ObjectText ( ). "ObjectText" XML-, , . , SP, , XML.

I tried several suggestions suggested on the web, such as casting / converting / replacing unified characters, XML, ascii, etc., and replaced invalid characters like && # xD ',' & # xA ',' & # x9 ', but unsuccessfully.

Thanks so much for helping me solve the problem. Thanks in advance

SELECT o.name as 'ObjectName', 
CASE o.xtype 
        WHEN 'C' THEN 'CHECK constraint ' 
        WHEN 'D' THEN 'Default or DEFAULT constraint'
        WHEN 'F' THEN 'FOREIGN KEY constraint'
        WHEN 'FN' THEN 'Scalar function'
        WHEN 'IF' THEN 'In-lined table-function'
        WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
        WHEN 'L' THEN 'Log'
        WHEN 'P' THEN 'Stored procedure'
        WHEN 'R' THEN 'Rule'
        WHEN 'RF' THEN 'Replication filter stored procedure' 
        WHEN 'S' THEN 'System table'  
        WHEN 'TF' THEN 'Table function' 
        WHEN 'TR' THEN 'Trigger'  
        WHEN 'U' THEN 'User table' 
        WHEN 'V' THEN 'View' 
        WHEN 'X' THEN 'Extended stored procedure' 
    ELSE o.xtype END as 'ObjectType',
    CAST(c.text as XML) as 'SearchObject'
    -- CAST(REPLACE(CAST(searchObject as NVARCHAR(MAX)), 'utf-8','utf-16') as XML) as 'SearchObject'

FROM syscomments c
    INNER JOIN sysobjects o ON c.id=o.id
    LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE o.xtype = 'P'
    and (c.text LIKE '%loan_timestamp%' or c.text LIKE '%aclk_timestamp%' or c.text LIKE '%addr_timestamp%')

"Message 9455, level 16, state 1, line 121, XML parsing: line 44, character 28, invalid qualified character name"

0
source

More articles:


All Articles