Pass XML to a stored procedure in node

Question

I am trying to figure out how to pass an XML value to a stored procedure using MSSQL node driver, from the documentation I can see that the driver does support stored procedures, and you also define custom data types like this:

sql.map.register(MyClass, sql.Text);

but I haven't found an example how can it be done for XML so far.

I did find a similar question but for a .NET SQL driver, trying to figure out if anyone has done this for Node.

UPDATE

I was able to send an XML to a stored procedure and parse it in DB, here's the example:

var request = new sql.Request(connection);
var xml = '<root><stock><id>3</id><name>Test3</name><ask>91011</ask></stock></root>'
request.input('XStock', sql.Xml, xml);

request.execute('StockUpdateTest', function (err, recordsets, returnValue, affected) {

});

Answer 1

I do not know this special case, but there are some general ideas:

The input parameter of a stored procedure, which should take some XML, can be either XML, VARCHAR or NVARCHAR. Well, just to mention this, VARBINARY might work too, but why should one do this...

A string in SQL-Server is either 8-bit encoded (VARCHAR) or 16-bit (NVARCHAR), XML is - in any case - NVARCHAR internally.

Most cases will be casted implicitly. You can pass a valid XML as VARCHAR or as NVARCHAR and assign this to a variable of type XML. Both will work. But you will get into troubles if your XML includes special characters...

Important: If the XML includes a declaration like <?xml ... encoding="utf-8"?> it must be handed over to the XML variable as VARCHAR, while utf-16 must be NVARCHAR. This declaration would be omitted in SQL Server in any case, so easiest is, to pass the XML as string without such a declaration.

The clear advise is, to pass an XML as 16-bit unicode string without <?xml ...?>-declaration. Doing so, there will be no implicit casting and you will not run in troubles with special characters and/or encoding issues.

Your SP can either define the parameter as XML or as NVARCHAR(MAX) and assign it to a typed variable internally.

Hope this helps!