You could use the XML data type in SQL Server 2005.

In SQL Server 2000, you can write something like:

exec sp_xml_preparedocument @h output, '<A name="n">

<B id="4">

<C something="else">

</C>

</B>

</A>'

select *

from openxml(@h, '/A/B',8)

WITH(name varchar(50) '../@name',

id int '@id',

xml varchar(50) '@mp:xmltext'

)

exec sp_xml_removedocument @h

and use a T-SQL string expression to get rid of the enclosing <B></B> tags
by trimming everthing up to the first > and starting from the last </
(assuming that you do not get <B/> back which you may if nothing is inside).

As an aside: Do not use the flag 3 if you can avoid it since it can have
some negative performance impact on your XPath performance.

Best regards
Michael

"Malin Davidsson" <malin.davidsson@aus.teleca.se> wrote in message
news:ebA4SUrCFHA.960@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I'm new at XML and have a question.
> I have the following code:
>
> <A name='n'>
> <B id=4>
> <C something='else'>
> </C>
> </B>
> <A>
>
> I use openxml to get name and id from A and B but I also want to get the
> text between <B> and </B>, "<C something='else'></C>"
>
> from:
>
> select *
> from openxml(@iDoc, '/A',3)
> WITH(name varchar(50) '@name',
> id int 'B/@id',
> xml varchar(50) '????????????'
> )
>
> I want the information:
>
> name='n'
> id=4
> xml='<c something='else'></c>'
>
>
> Is this possible? and how can I do that?
>
> Thanks for any help!
>
> // Malin
>