Collate while parsing xml

Question

On same server I have few databases with diferent collation. I'm parsing xml as

CREATE TABLE #tblDocuments
(
    Document varchar(5) COLLATE DATABASE_DEFAULT
   ,DocCode varchar(15) COLLATE DATABASE_DEFAULT
   ,Ammount decimal(18,2)
   );

IF @@ERROR <> 0
    GOTO ErrorHandl;

INSERT INTO #tblDocuments
(
    Document
   ,DocCode
   ,Ammount
)

SELECT 
    Tbl.Col.value('@DocType','varchar(3)') AS Document
   ,Tbl.Col.value('@DocCode','varchar(15)') AS DocCode
   ,Tbl.Col.value('@Ammount','decimal(18,2)') AS Ammount
FROM 
    @xmlData.nodes('/Documents/document') AS Tbl(Col);

Is there a way to parse xml without first creating table with predefined collation. Something like (I know this code isn't working)

SELECT 
    Tbl.Col.value('@DocType','varchar(3) COLLATE DATABASE_DEFAULT') AS Document
   ,Tbl.Col.value('@DocCode','varchar(15) COLLATE DATABASE_DEFAULT') AS DocCode
   ,Tbl.Col.value('@Ammount','decimal(18,2) COLLATE DATABASE_DEFAULT') AS Ammount
INTO #tblDocuments
FROM 
    @xmlData.nodes('/Documents/document') AS Tbl(Col);enter code here

Thx in advance

Solomon Rutzky · Accepted Answer · 2017-01-11T15:42:35.543

Put the COLLATE outside of the .value() function:

SELECT 
    Tbl.Col.value('@DocType','varchar(3)') COLLATE DATABASE_DEFAULT AS [Document]
   ,Tbl.Col.value('@DocCode','varchar(15)') COLLATE DATABASE_DEFAULT AS [DocCode]
   ,Tbl.Col.value('@Ammount','decimal(18,2)') AS Ammount
INTO #tblDocuments
FROM 
    @xmlData.nodes('/Documents/document') AS Tbl(Col);
    enter code here

Also, you don't use COLLATE on anything but string datatypes (CHAR / VARCHAR / NCHAR / NVARCHAR -- and even the deprecated TEXT and NTEXT datatypes).

Collate while parsing xml

1 Answers1