Home SQL - cast XML as nvarchar(max)

# SQL - cast XML as nvarchar(max)

dns_nx
1#
dns_nx Published in 2018-01-11 15:41:50Z
 I've got a problem with an SQL stored procedure. I need to generate a xml from a database. In my stored procedure, I collect all information and then my plan was to generate the XML file as I need it (see code). All transactions I need to report are loaded into the variable @transactionXml. It is of data type xml and can become quite big. SET @xmlOut += '' + cast(CAST(getdate() as date) as nvarchar(100)) + '' SET @xmlOut += '' + @noOfResults + '' SET @xmlOut += '' + cast(@fromDate as nvarchar(max)) + '' SET @xmlOut += '' + cast(@toDate as nvarchar(max)) + '' SET @xmlOut += '' + @minInvoiceNo + '' SET @xmlOut += '' + @maxInvoiceNo + '' SET @xmlOut += cast(@transactionXml as nvarchar(max)) SET @xmlOut += '' SELECT @xmlOut  When I try to cast the datatype to nvarchar(max) (as in the code section), the string is not complete and missing some information. It seems, that the string is cut after x-signs. Is there a way to cast the @transactionXml variable completely to text, so that I can use it in my @xmlOut statement?
Shnugo
2#
 You should not create an XML on string level! Try this: SELECT CAST(getdate() as date) AS export_datuma ,@noOfResults AS export_szla_db ,@fromDate AS kezdo_ido ,@toDate AS zaro_ido ,@minInvoiceNo AS kezdo_szla_szam ,@maxInvoiceNo AS zaro_szla_szam ,@transactionXml AS [*] --This is the pre-created XML which should be fine FOR XML PATH('szamlak');  The pre-created XML must be fine, otherwise you could not store it as XML native type. Creating the XML on string level can have various dangerous side effects. Just imagine a forbidden character within one of your variables...