Retrieve data from text field containing XML data

I have the following data in a SQL2k data field:
<History data="<?xml version="1.0" encoding="utf-16"?>
<ColumnsList xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<column id="Availability" v="U" o="A" />
<column id="LastModificationAgentID" v="59930" />
</ColumnsList>"/>
I'm trying to retrieve the data with Query Analyzer using the following quer
y:
SELECT *
FROM OPENXML(Column, N'/ColumnsList/Column')
WITH (ID varchar(40), v varchar(100), o varchar(100))
Am I at all on the right track with this? This is my first attempt at
working with XML and SQL together.
Thanks in advance.I am a bit . Your XML is not a well-formed XML document.
Also, I would recommend that you look at OpenXML samples in Books Online.
Basically, OpenXML uses a handle to refer to the XML document that it gets
from sp_xml_preparedocument (which parses the XML) and needs to be released
by sp_xml_removedocument.
If your XML data in the column is larger than 8k, you will have an
implementation restriction in SQL Server 2000 on variables not allowing
NTEXT/TEXT types. This makes it harder to use OpenXML on already stored XML
data (I recommend to use OpenXML on the data when it is being passed to a
stored proc before it gets stored in a table).
I would also recommend to look at SQL Server 2005, that have much better
support for your scenario (look at the XML datatype, XQuery and the nodes()
method).
Best regards
Michael
"JAdams" <JAdams@.discussions.microsoft.com> wrote in message
news:E8F3E463-2071-41EA-91E1-49E9E5D39510@.microsoft.com...
>I have the following data in a SQL2k data field:
> <History data="<?xml version="1.0" encoding="utf-16"?>
> <ColumnsList xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <column id="Availability" v="U" o="A" />
> <column id="LastModificationAgentID" v="59930" />
> </ColumnsList>"/>
> I'm trying to retrieve the data with Query Analyzer using the following
> query:
> SELECT *
> FROM OPENXML(Column, N'/ColumnsList/Column')
> WITH (ID varchar(40), v varchar(100), o varchar(100))
>
> Am I at all on the right track with this? This is my first attempt at
> working with XML and SQL together.
> Thanks in advance.
>