Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Return Child rows on a poorly typed column :) Expand / Collapse
Author
Message
Posted Tuesday, December 24, 2013 3:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 10:34 AM
Points: 4, Visits: 38
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?
Post #1525818
Posted Tuesday, December 24, 2013 9:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 10:34 AM
Points: 4, Visits: 38
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....
Post #1525853
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse