Sqlxml Without Xml Encoding

SQLXML without XML encoding?

If you specify type as an option to for xml, you can use an XPath query to convert the XML type back to a varchar. With an example table variable:

declare @MyTable table (id int, name varchar(50))

insert @MyTable (id, name) select 1, 'Joel & Jeff'
union all select 1, '<<BIN LADEN>>'
union all select 2, '&&BUSH&&'

One possible solution is:

select  b.txt.query('root').value('.', 'varchar(max)')
from    (
        select  distinct id
        from    @MyTable
        ) a
cross apply
        (
            select  CASE ROW_NUMBER() OVER(ORDER BY id) WHEN 1 THEN '' 
                        ELSE ', ' END + name
        from    @MyTable
        where   id = a.id
        order by 
                id
        for xml path(''), root('root'), type
        ) b(txt)

This will print:

Joel & Jeff, <<BIN LADEN>>
&&BUSH&&

Here's an alternative without XML conversions. It does have a recursive query, so performance mileage may vary. It's from Quassnoi's blog:

;WITH   with_stats(id, name, rn, cnt) AS
        (
        SELECT  id, name,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY name),
                COUNT(*) OVER (PARTITION BY id)
        FROM    @MyTable
        ),
        with_concat (id, name, gc, rn, cnt) AS
        (
        SELECT  id, name,
                CAST(name AS VARCHAR(MAX)), rn, cnt
        FROM    with_stats
        WHERE   rn = 1
        UNION ALL
        SELECT  with_stats.id, with_stats.name,
                CAST(with_concat.gc + ', ' + with_stats.name AS VARCHAR(MAX)),
                with_stats.rn, with_stats.cnt
        FROM    with_concat
        JOIN    with_stats
        ON      with_stats.id = with_concat.id
                AND with_stats.rn = with_concat.rn + 1
        )
SELECT  id, gc
FROM    with_concat
WHERE   rn = cnt
OPTION  (MAXRECURSION 0)

How can I avoid encoding when using FOR XML PATH ?

The point is: XML is not text with some extra characters!

Text within XML must not carry characters needed for the markup, especially the three chars of evil: <>&. You are not adding XML to your output but a string, that looks like XML. And the engine does what must be done: This string is encoded.

The path around: You must hand over XML instead of a string.

Try to replace this line

, td = N'<a href="' + Website + '">' + Company + N'</a>', ''

with this

,td = (SELECT Website AS [a/@href],Company AS a FOR XML PATH(''),TYPE)

UPDATE

Don't know if this would help you, but here you'll find a generic function which will create a XHTML table with header and body generically directly from a SELECT statement.

Avoid encoding of ampersand in SQL XML attribute

The result you get unfortunately and is re-encoded and invalid is perfectly correct - but not what you expect. You pass in € which is a string. Within XML this is escaped as € and will be re-coded to €.

You must stop to think of XML as kind of formalized string. This is a technical issue. XML will handle this implicitly.

There are two ways:

• Go the string-way and cast your XML to NVARCHAR, do any string manipulation you might want (e.g. REPLACE(myXML,'€','€') and cast back to XML or

• (I'd prefer this!) hand in the € as actual symbol and let the XML engine do the encoding.

EDIT

One more thing: SQL Server doesn't know the € entity. Try with € or €:

SELECT '€' AS [@EuroSign]               --works
      ,'€' AS [@NamedEscapedEuro]  --will be encoded
      ,'€' AS [@EscapedEuro]      --will be encoded
FOR XML PATH('TestEuro'),ROOT('root')

SELECT --CAST('<x>'+'€'+'</x>' AS XML).value('/x[1]','nvarchar(10)') AS [@EuroSign]              --not allowed!!!
       --CAST('<x>'+'€'+'</x>' AS XML).value('/x[1]','nvarchar(10)') AS [@NamedEscapedEuro] --not allowed, exists, but not known in SQL Server!
       CAST('<x>'+'€'+'</x>' AS XML).value('/x[1]','nvarchar(10)') AS [@EscapedEuro]       --works
FOR XML PATH('TestEuro'),ROOT('root') 

SqlXml removes XML header when comes from string

If you cannot change the type of your SqlXml attribute, you could try converting the SqlXml to xml document to append xml declaration and get the outer xml:

public string SqlXmlToString(SqlXml sqlXml)
{
    XmlDocument doc = new XmlDocument();
    doc.Load(sqlXml.CreateReader());

    // Create XML declaration with your encoding. 
    XmlDeclaration xmldecl;
    xmldecl = doc.CreateXmlDeclaration("1.0", null, null);
    xmldecl.Encoding = "UTF-8";

    // Add to the document the created declaration
    XmlElement root = doc.DocumentElement;
    doc.InsertBefore(xmldecl, root);

    return doc.OuterXml;
}

Hope this is helpfully

How to solve unable to switch the encoding error when inserting XML into SQL Server

Although a .net string is always UTF-16 you need to serialize the object using UTF-16 encoding.
That sould be something like this:

public static string ToString(object source, Type type, Encoding encoding)
{
    // The string to hold the object content
    String content;

    // Create a memoryStream into which the data can be written and readed
    using (var stream = new MemoryStream())
    {
        // Create the xml serializer, the serializer needs to know the type
        // of the object that will be serialized
        var xmlSerializer = new XmlSerializer(type);

        // Create a XmlTextWriter to write the xml object source, we are going
        // to define the encoding in the constructor
        using (var writer = new XmlTextWriter(stream, encoding))
        {
            // Save the state of the object into the stream
            xmlSerializer.Serialize(writer, source);

            // Flush the stream
            writer.Flush();

            // Read the stream into a string
            using (var reader = new StreamReader(stream, encoding))
            {
                // Set the stream position to the begin
                stream.Position = 0;

                // Read the stream into a string
                content = reader.ReadToEnd();
            }
        }
    }

    // Return the xml string with the object content
    return content;
}

By setting the encoding to Encoding.Unicode not only the string will be UTF-16 but you should also get the xml string as UTF-16.

<?xml version="1.0" encoding="utf-16"?>

Oracle SQL XML Offending characters in XML file (UTF-8)

Thanks for all your replies. In the end I put the below into my PL/SQL, as like some of you said even though you have version '1.0" encoding="UTF-8" in your SQL XML code the file was being stored in a different encoding. So I needed to force it to write/store an XML file in UTF-8 format.

If you look up DBMS_XSLPROCESSOR.clob2file there are a number of parameters passed to this procedure, one being the character set to use for the output file. Which in this case for UTF-8 it was nls_charset_id('AL32UTF8').

DBMS_XSLPROCESSOR.clob2file(l_clob, l_directory, l_file_name||'.xml',nls_charset_id('AL32UTF8'));

thanks Guys

---

Related Topics