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?