Home SQL - cast XML as nvarchar(max)
Reply: 0

SQL - cast XML as nvarchar(max)

user1467
1#
user1467 Published in August 15, 2018, 8:44 pm

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 += '<export_datuma>' + cast(CAST(getdate() as date) as nvarchar(100)) + '</export_datuma>'
SET @xmlOut += '<export_szla_db>' + @noOfResults + '</export_szla_db>'
SET @xmlOut += '<kezdo_ido>' + cast(@fromDate as nvarchar(max)) + '</kezdo_ido>'
SET @xmlOut += '<zaro_ido>' + cast(@toDate as nvarchar(max)) + '</zaro_ido>'
SET @xmlOut += '<kezdo_szla_szam>' + @minInvoiceNo + '</kezdo_szla_szam>'
SET @xmlOut += '<zaro_szla_szam>' + @maxInvoiceNo + '</zaro_szla_szam>'
SET @xmlOut += cast(@transactionXml as nvarchar(max))
SET @xmlOut += '</szamlak>'

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?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.309384 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO