ExecuteXmlReader

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has some data that is being read is a column with XML
data. It is typed 'text' and the server is running 2000 so I don't have an
option to type the column as XML. I would however like to use
ExecuteXmlReader but I get an exception indicating that the data must be XML.
How do I convince the client that it is XML data?
My current query looks like:

SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
OrderNumber='yyy'

Right now I am doing ExecuteScalar to read the XML string in. It seems silly
to read in a large chunk of "string" data, pass it to a MemoryStream, build
an XmlReader, and then use that to build an XML document (specifically an
XPath document because I want to do some XPath searches on the data). If I
can get an XmlReader directly from the SQL query via ExecuteXmlReader it
seems more efficient. Is this possible with the data structured as I
indicated? I have looked at FOR XML but I have not had good luck yet.

Any suggestions?

Thank you.

Kevin
 
So, something like this isn't working?

SELECT XMLSent FROM OutboundTable WHERE documentTransactionID = xxx AND
OrderNumber = 'yyy' FOR XML AUTO
 
The problem with this is that the whole Xml string is returned as an
attribute. This column contains an XML document as text I would like to
create an XmlReader that is positioned on this XML string like the column
data itself is an XML document. Is that possible?

Kevin
 
When I do something like:

SELECT CAST(XMLSent AS xml) AS XMLSent FROM OutboundTable WHERE
documentTransactionID = xxx AND OrderNumber = 'yyy'

I get an error

Msg 9420, Level 16, State 1, Line 1
XML parsing: line 54, character 141, illegal xml character

THere are illegal characters in the data but I would like to remove them
without having to read the whole string in removing them and then passing the
string to build an XmlReader. If I read the whole string in and create a
memory stream then create an XmlReader from that I don't get these errors. Is
there a way to turn XML vlidation off or intercept the illegal characters as
they are encountered and replace them?

Kevin
 
Back
Top