April 22, 2021 at 4:10 pm
Hi there
I would like to cast a NVARCHAR(MAX) variable string to an XML variable as follows:
declare @DataSheetXML NVARCHAR(MAX) = '<Datasheet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Attributes><ControlList/><AttributeList><AttributeDefinition><Name>supports_alarms</Name><Value>True</Value></AttributeDefinition><AttributeDefinition><Name>alarms</Name><Value>Tinylike</Value></AttributeDefinition><AttributeDefinition><Name>supports_min_max</Name><Value>False</Value></AttributeDefinition><AttributeDefinition><Name>locally_latched_alarms</Name><Value>False</Value></AttributeDefinition><AttributeDefinition><Name>supports_start</Name><Value>False</Value></AttributeDefinition><AttributeDefinition><Name>supports_suspend_when_full</Name><Value>False</Value></AttributeDefinition><AttributeDefinition><Name>rtc</Name><Value>internal</Value></AttributeDefinition><AttributeDefinition><Name>has_radio</Name><Value>True</Value></AttributeDefinition><AttributeDefinition><Name>unicode_descriptions</Name><Value>True</Value></AttributeDefinition><AttributeDefinition><Name>minimum_interval</Name><Value>120</Value></AttributeDefinition><AttributeDefinition><Name>host_battery_alert_function</Name><Value>battery_alert</Value></AttributeDefinition><AttributeDefinition><Name>memory_size</Name><Value>1146880</Value></AttributeDefinition><AttributeDefinition><Name>pt1000_reference_resistor</Name><Value>1831.1688311688313</Value></AttributeDefinition><AttributeDefinition><Name>device_description</Name><Value>Tinytag Ultra2 Radio Receiver</Value></AttributeDefinition><AttributeDefinition><Name>is_basestation</Name><Value>True</Value></AttributeDefinition><AttributeDefinition><Name>memory_size</Name><Value>32768</Value></AttributeDefinition><AttributeDefinition><Name>config_hz</Name><Value>1024</Value></AttributeDefinition><AttributeDefinition><Name>delay_hz</Name><Value>1</Value></AttributeDefinition><AttributeDefinition><Name>max_delay</Name><Value>315360000</Value></AttributeDefinition><AttributeDefinition><Name>outputs_signed_encoding</Name><Value>True</Value></AttributeDefinition><AttributeDefinition><Name>has_32_bit_addresses</Name><Value>True</Value></AttributeDefinition><AttributeDefinition><Name>has_bc_start_new_session</Name><Value>True</Value></AttributeDefinition></AttributeList><GroupsUsingAmbientInterval><string>service</string><string>service</string><string>ambient</string><string>service</string></GroupsUsingAmbientInterval><GroupsUsingInstrumentationInterval><string>instrumentation</string><string>instrumentation</string><string>test</string><string>test</string><string>test</string><string>test</string><string>test</string><string>test</string><string>test</string><string>test</string><string>test</string><string>test</string><string>test</string></GroupsUsingInstrumentationInterval><SupportedNetworkClasses><string>0</string><string>1</string></SupportedNetworkClasses><AdjustmentGain/><AdjustmentRange/><DewpointPairs><ArrayOfString><string>th</string><string>rh</string></ArrayOfString></DewpointPairs><InterfaceOrderNameMap/><OutputsEnabled/><ChannelGroups><string>instrumentation</string><string>ambient</string><string>test</string><string>service</string></ChannelGroups></Attributes><InterfaceList><InterfaceDefinition><id>loaded_vrefswitched</id><bits>12</bits><fixed_bits>0</fixed_bits><signed>false</signed><total_bits>0</total_bits></InterfaceDefinition><InterfaceDefinition><id>vrefswitched</id><bits>12</bits><fixed_bits>0</fixed_bits><signed>false</signed><total_bits>0</total_bits></InterfaceDefinition><InterfaceDefinition><id>bat_chk</id><bits>12</bits><fixed_bits>0</fixed_bits><fnName>twelve_bit_raw</fnName><signed>false</signed><total_bits>0</total_bits></InterfaceDefinition><InterfaceDefinition><id>temperature</id><bits>12</bits><fixed_bits>0</fixed_bits><signed>false</signed><total_bits>0</total_bits></InterfaceDefinition><InterfaceDefinition><id>RadioStats_00</id><bits>16</bits><fixed_bits>0</fixed_bits><signed>false</signed><total_bits>0</total_bits></InterfaceDefinition><InterfaceDefinition><id>Radio'
declare @xml xml
Set @xml = cast(@DataSheetXML as XML)
However I am getting the following error:
Msg 9400, Level 16, State 1, Line 6
XML parsing: line 1, character 4000, unexpected end of input
What am I doing wrong here?
April 22, 2021 at 6:12 pm
Quick look at your code, your XML is missing closing tags, so it is malformed XML and as such cannot be cast to XML.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 23, 2021 at 8:28 am
Hi Brian
Ok what closing tags do I need at the end of my XML?
April 23, 2021 at 8:54 am
Your missing half the document, right at the end you open a new InterfaceDefinition and then don't close it, you also done close the datasheet, that is not a full and complete XML doc so you will need to go and fix the data in the source system to then be able to convert it to xml
As you can see by the formatted XML your missing everything at the bottom of the doc after the last <InterfaceDefinition><id>Radio'
April 23, 2021 at 2:04 pm
Your XML ends with:
<InterfaceDefinition><id>Radio
so right away, I see that id and InterfaceDefinition are both unclosed. I expect these are not root level XML tags, so you probably have more that is unclosed.
Depending on where you are getting the data (flat file, manual entry, or a table column), it could be a problem with copy-paste, could be a problem that the tool truncated some of the data and it is now lost, or it could be that whoever entered the data entered incomplete data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 23, 2021 at 5:03 pm
To better understand the problem and solution have a look at Well Formed XML:
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply