ExecuteXmlReader

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

Brendan
Mon Jul 10 00:04:46 CDT 2006

So, something like this isn't working?

SELECT XMLSent FROM OutboundTable WHERE documentTransactionID = xxx AND
OrderNumber = 'yyy' FOR XML AUTO

"Kevin Burton" <KevinBurton@discussions.microsoft.com> wrote in message
news:24BE2514-A33C-4347-8C2D-678122817DCD@microsoft.com...
>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
>

KevinBurton
Tue Jul 11 17:49:01 CDT 2006

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

"Brendan Green" wrote:

> So, something like this isn't working?
>
> SELECT XMLSent FROM OutboundTable WHERE documentTransactionID = xxx AND
> OrderNumber = 'yyy' FOR XML AUTO
>
> "Kevin Burton" <KevinBurton@discussions.microsoft.com> wrote in message
> news:24BE2514-A33C-4347-8C2D-678122817DCD@microsoft.com...
> >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
> >
>
>
>

KevinBurton
Tue Jul 11 18:15:02 CDT 2006

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

"Brendan Green" wrote:

> So, something like this isn't working?
>
> SELECT XMLSent FROM OutboundTable WHERE documentTransactionID = xxx AND
> OrderNumber = 'yyy' FOR XML AUTO
>
> "Kevin Burton" <KevinBurton@discussions.microsoft.com> wrote in message
> news:24BE2514-A33C-4347-8C2D-678122817DCD@microsoft.com...
> >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
> >
>
>
>