How to eliminate XML Escape Characters in "FOR XML PATH"?

How to eliminate XML Escape Characters (" ",' '< <> >& &) after using "FOR XML PATH", With out using "REPLACE" Command.

-- Tables Creation
IF OBJECT_ID('TempDB..#Test') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test (ID INT, Name VARCHAR(30))
-- Sample Data
INSERT INTO #Test(ID, Name) VALUES(1,'Test'),(2,'&Test'), (3,'')
-- Actual Data
SELECT ID, Name FROM #test

Required Output

Test,&Test,

Se eseguo questo codice:

SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH('')),1,1,'')

Current Output

Test,&Test,

Solution:

SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') AS 'NameList'

L'alias [text()] pare si possa omettere:

SELECT STUFF((SELECT ','+Name FROM #Test FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') AS 'NameList'

Da approfondire, ma la magia è fatta dal "value() Method":
MSDN page for "value() Method (xml Data Type)"



Trovato qui.

---

Se dovesse dare questo errore:

Impossibile eseguire SELECT perché le impostazioni delle opzioni SET seguenti non sono corrette: 'ARITHABORT'. Verificare che tali opzioni SET siano corrette per l'utilizzo con viste indicizzate e/o indici su colonne calcolate e/o indici filtrati e/o notifiche delle query e/o metodi per tipi di dati XML e/o operazioni sugli indici spaziali.

Prima della SELECT bisogna impostare SET ARITHABORT ON:

SET ARITHABORT ON

SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') AS 'NameList'

docs.microsoft.com: set-arithabort-transact-sql

DataTable to Excel

Non è testato al 100%!!
Bisogna parametrizzare (quantomeno il path di output)!

Private Shared Sub toExcel(ByVal dt As DataTable)

        Dim application As New Excel.Application()
        Dim workbook As Excel.Workbook = application.Workbooks.Add()
        Dim worksheet As Excel.Worksheet = CType(workbook.Sheets(1), Excel.Worksheet)

        For c As Integer = 0 To dt.Columns.Count - 1
            For r As Integer = 0 To dt.Columns.Count - 1
                worksheet.Cells(r + 1, c + 1) = dt.Rows(r).Item(c).ToString
            Next
        Next

        workbook.SaveAs("C:\whatever123.xlsx")
        workbook.Close()

        Marshal.ReleaseComObject(application)
    End Sub

    Private Shared Function GetExcelColumnName(ByVal columnNumber As Integer) As String

        Dim dividend As Integer = columnNumber
        Dim columnName As String = [String].Empty
        Dim modulo As Integer

        While dividend > 0
            modulo = (dividend - 1) Mod 26
            columnName = Convert.ToChar(65 + modulo).ToString() & columnName
            dividend = CInt((dividend - modulo) / 26)
        End While

        Return columnName

    End Function



    '=======================================================
    'Service provided by Telerik (www.telerik.com)
    'Conversion powered by NRefactory.
    'Twitter: @telerik
    'Facebook: facebook.com/telerik
    '=======================================================

Trovato qui.