SQL - cast XML as nvarchar(max)

Question

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?

How are you verifying that the string is "not complete"? If you are outputting to Management Studio, it is simply truncating the string for presentation. — Aaron Bertrand, Jan 11 '18 at 15:44
Is it really? This was my hint, that the string(xml) is not complete. — dns_nx, Jan 11 '18 at 15:45
By "how" I meant *where*. If in Management Studio, it's not the string's fault, it's the tool's. I wrote about this [here](https://www.mssqltips.com/sqlservertip/3185/validate-the-contents-of-large-dynamic-sql-strings-in-sql-server/). — Aaron Bertrand, Jan 11 '18 at 15:46
There is a closing `` without the opening tag? Did you miss to copy the first line or is it really missing? — Shnugo, Jan 11 '18 at 15:59

Shnugo · Accepted Answer · 2018-01-11T16:27:07.900

4

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...

edited Jan 11 '18 at 16:27

answered Jan 11 '18 at 16:02

Shnugo

66,100
9
53
114

That's a really good solution. Thanks very much! Is there also a way to include before the data ``?` – dns_nx Jan 11 '18 at 16:20
@dns_nx [Read this answer](https://stackoverflow.com/a/37477855/5089204). In short: The XML's declaration is **never** part of a *native-type-XML* within SQL Server. It is a hint about the encoding of a file. SQL-Server's XML is encoded `UTF-16 / UCS-2` in any case. A differing declaration would be wrong. You can add any string fragment after a `CAST` to `NVARCHAR(MAX)`, but be sure to store this as `UTF-8` encoded file, if you write this into the declaration. Many people take this declaration just as some *fancy extra*... – Shnugo Jan 11 '18 at 16:32
Ok. Thanks for the quick info. – dns_nx Jan 11 '18 at 16:54

SQL - cast XML as nvarchar(max)

1 Answers1