(Probably not related to C#, simulated also in SSMS using the SQL language)
I am tayloring the C# command-line utility to process the data, store them in SQL Server tables, and then to convert it to XML, and store it in another table.
When doing that, I get this exception:
System.Data.SqlClient.SqlException: 'XML parsing: line 1, character 16, undeclared prefix'
However, I am using exactly the same approach in another utility written for another customer -- no problems are observed in the same situation. The truth is that the utility is older (compiled few years ago) and it works in a bit different environment.
More details
The application collects data from a text file (DELFOR message, but that should not be important), stores the headers and the data separately into the dedicated SQL tables, and then the user-defined SQL function ufn_delfor_message_to_xml(@message_reference_number, ...) is used to get the XML form of the joined tables.
The XML result should be obtained as a string into the C# code:
string xmlFormOfTheMessage = DbUtil.ExecuteScalar(
"SELECT dbo.ufn_delfor_message_to_xml(@message_reference_number, @xml_nad_info)",
new Hashtable {
{ "message_reference_number", UniqueMessageReferenceNumber() },
{ "xml_nad_info", xmlNadInfo }
}).ToString();
where the DbUtil.ExecuteScalar() is my helper to wrap the Microsoft System.Data.SqlClient.SqlCommand.ExecuteScalar() (this works in many other situations, no error expected in the wrapper).
When trying to find the reason, I got rid of the C# code using only the SQL Server Management Studio (SSMS) to duplicate the error.
When using the code:
DECLARE @message_reference_number varchar(55) = '1_022723.4443311'
DECLARE @xml_nad_info XML = '<delfor_nad_info><nad_info><party_identifier>0941B66333840</party_identifier><name>22723</name><street>xxxxxx</street><city>yyy</city><postal_code>08191</postal_code><country_identifier>ES</country_identifier><duns></duns><created>2022-09-06T20:02:46</created></nad_info></delfor_nad_info>'
DECLARE @x xml
SET @x = dbo.ufn_delfor_message_to_xml(@message_reference_number, @xml_nad_info)
SELECT @x
it works just fine. The final SELECT @x shows the XML representation. However, when avoiding the @x XML variable and moving the function to the SELECT, the same error appears:
SELECT dbo.ufn_delfor_message_to_xml(@message_reference_number, @xml_nad_info)
For the case, the full code of the SQL function looks like this:
CREATE FUNCTION dbo.ufn_delfor_message_to_xml
(@message_reference_number varchar(55), -- grp+UNH+documentID
@xml_nad_info XML)
RETURNS XML
AS
BEGIN
DECLARE @xml_scheduling XML = (
SELECT -- [message_reference_number],
--,[document_id],
--,[line_item_identifier],
--[item_identifier_by_buyer],
delivery_plan_commitment_level_code,
datetime1,
datetime1_function_code_qualifier,
frequency_code,
quantity,
unit_code,
datetime2_function_code_qualifier,
datetime2
FROM delfor_scheduling_data AS scheduling
WHERE message_reference_number = @message_reference_number
ORDER BY datetime1
FOR XML AUTO, ELEMENTS)
-- SELECT @xml_scheduling
DECLARE @xml_result XML
;WITH XMLNAMESPACES ('http://schema.xxxxxxx.xx/dm/externalMessage' AS dat)
SELECT @xml_result = (
SELECT [interchange_sender],
[interchange_recipient],
[datetime_of_preparation],
[interchange_control_reference_id],
[dat:work_order].[message_reference_number],
[dat:work_order].[document_id],
[message_function_code],
[document_issue_datetime],
[type_of_instruction],
[dat:work_order].grp,
[dat:work_order].[seller_identifier],
[dat:work_order].[shipfrom_identifier],
[dat:work_order].[plant_identifier],
[dat:work_order].[buyer_identifier],
@xml_nad_info,
[dat:detail].shipto_identifier,
[dat:detail].[item_identifier_by_buyer],
[dat:detail].[item_identifier_by_seller],
[dat:detail].[engineering_change_number],
[dat:detail].[other_material_characteristics],
[dat:detail].[material_description],
[dat:detail].[is_supply_for_consignment],
[dat:detail].[location_of_discharge_code],
[dat:detail].[purchase_order_number],
[dat:detail].[document_line_identifier],
[dat:detail].[transport_document_reference],
[dat:detail].[new_delivery_schedule_number],
[dat:detail].[new_delivery_instruction_date],
[dat:detail].[previous_delivery_schedule_number],
[dat:detail].[previous_delivery_instruction_date],
[dat:detail].[planner_contact_employee_code],
[dat:detail].[planner_contact_employee_name],
[dat:detail].[cumulative_quantity_received],
[dat:detail].[cumulative_quantity_received_unit_code],
[dat:detail].[cumulative_quantity_received_date],
[dat:detail].[cumulative_quantity_scheduled],
[dat:detail].[cumulative_quantity_scheduled_unit_code],
[dat:detail].[cumulative_quantity_scheduled_date],
[dat:detail].[cumulative_quantity_received_and_accepted],
[dat:detail].[cumulative_quantity_received_and_accepted_unit_code],
[dat:detail].[last_received_delivery_note_number],
[dat:detail].[last_received_delivery_note_date],
@xml_scheduling AS [dat:address_info]
FROM delfor_message_info AS [dat:work_order]
JOIN delfor_scheduled_article_details AS [dat:detail]
ON [dat:detail].message_reference_number = [dat:work_order].message_reference_number
AND [dat:detail].document_id = [dat:work_order].document_id
AND [dat:work_order].message_reference_number = @message_reference_number
FOR XML AUTO, ELEMENTS
)
RETURN @xml_result
END
Can you suggest what could cause the exception? Why the older utility in the older environment works in the same situation? Could there be any new restrictions in the newer environment that would cause the exception?