Return Child rows on a poorly typed column :)

• dmaslowski

  SSC Veteran

  Points: 230

  More actions

  December 24, 2013 at 3:36 pm

  #148410

  I need to return all of the child values for linenumbers in a XML Document that is stored in a table.

  My Schema has xsd:anyType for most of the columns.

  When I run this query:

  SELECT

  line.value('(/lineNumber)', 'varchar(7)') as LineNumber

  FROM dbo.tBrokerInstructions

  CROSS APPLY XML_data.nodes('/brokerInstructions/brokerInstructionsLine') as Lines(line)

  I get "XQuery [dbo.tBrokerInstructions.xml_data.value()]: Cannot atomize/apply data() on expression that contains type 'lineNumber' within inferred type 'element(lineNumber,#anonymous) *'"

  Can I use the string function to get this to work?

  Any help?

• dmaslowski

  SSC Veteran

  Points: 230

  More actions

  December 24, 2013 at 9:57 pm

  #1675862

  I hacked a way using OPENXML. It wasn't pretty.

  If I had loaded the XML to a variable would the bad schema still have stuck?

  Thanks....