|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ExecuteXmlReaderdata. 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 Show quote "Kevin Burton" <KevinBur***@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 > 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 Show quote "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" <KevinBur***@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 > > > > > 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 Show quote "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" <KevinBur***@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 > > > > >  |
|||||||||||||||||||||||
Other interesting topics