Cant cast VARCHAR(MAX) To XML Variable

  • 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?

  • Quick look at your code, your XML is missing closing tags, so it is malformed XML and as such cannot be cast to XML.

     

  • Hi Brian

    Ok what closing tags do I need at the end of my XML?

  • 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'

    Attachments:
    You must be logged in to view attached files.
  • 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.

  • To better understand the problem and solution have a look at Well Formed XML:

     

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply