将 XML 传递给节点中的存储过程 [英] Pass XML to a stored procedure in node
问题描述
我想弄清楚如何使用 MSSQL 节点驱动程序，从文档中我可以看到该驱动程序确实支持存储过程，并且您还定义了这样的自定义数据类型:
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);
但到目前为止我还没有找到如何为 XML 完成的示例.
but I haven't found an example how can it be done for XML so far.
我确实找到了一个类似的问题，但对于 .NET SQL 驱动程序，我试图找出是否有人这样做过对于节点.
I did find a similar question but for a .NET SQL driver, trying to figure out if anyone has done this for Node.
更新
我能够将 XML 发送到存储过程并在数据库中解析它，示例如下:
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) {
});
推荐答案
我不知道这个特殊情况，但有一些大致的想法:
I do not know this special case, but there are some general ideas:
存储过程的输入参数，需要一些XML，可以是XML、VARCHAR或NVARCHAR.好吧，只是提一下，VARBINARY 也可能有效，但为什么要这样做...
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...
SQL-Server 中的字符串是 8 位编码 (VARCHAR) 或 16 位 (NVARCHAR)，XML 是 - 在任何情况下 -NVARCHAR 内部.
A string in SQL-Server is either 8-bit encoded (VARCHAR) or 16-bit (NVARCHAR), XML is - in any case - NVARCHAR internally.
大多数情况将被隐式转换.您可以将有效的 XML 作为 VARCHAR 或作为 NVARCHAR 传递，并将其分配给 XML 类型的变量.两者都会起作用.但是如果您的 XML 包含特殊字符，您就会遇到麻烦...
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...
重要:如果 XML 包含像 <?xml ... encoding="utf-8"?> 这样的声明，它必须strong> 交给 XML 变量为 VARCHAR，而 utf-16 必须 为 NVARCHAR.在任何情况下，SQL Server 中都会省略此声明，因此最简单的方法是将 XML 作为字符串传递而无需此类声明.
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.
明确的建议是，将 XML 作为 16 位 unicode 字符串传递，而无需 <?xml ...?>-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.
您的 SP 可以将参数定义为 XML 或 NVARCHAR(MAX) 并在内部将其分配给类型化变量.
Your SP can either define the parameter as XML or as NVARCHAR(MAX) and assign it to a typed variable internally.
希望这会有所帮助！
这篇关于将 XML 传递给节点中的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
