SQL Server 2008: Retrieve XML column value from the ResultSet and store it in a Document

Question

I want to retrieve the value of a column with xml datatype.I have researched about the query method for retrieval but not quite sure whether I can use it to retrieve untyped xml values. Also clueless about retrieving the xml column value from the resultset. Upon retrieval, I want to store it in a Document for parsing.

CREATE TABLE [upd].[PRPMST](
[ETMADD] [nvarchar](16) NOT NULL,
[ETDXML] [xml] NOT NULL)

I would like to retrieve the column value ETMADD The xml that will be contained in this column looks like this:

<root>
  <PRPMST>
    <DownloadEnvironment>UAT</DownloadEnvironment>
    <RetentionPeriod>2</RetentionPeriod>
    <DownloadAttempts>2</DownloadAttempts>
    .
    .
    .
  </PRPMST>
</root>

Answer 1

Either you can take it as usual binary or char value, like this:

SELECT ETMADD, CAST(ETDXML as nvarchar(max)) as XmlDoc
from [upd].[PRPMST]

OR

if you need values from Xml column, try to do this, for example:

SELECT 
  * ,
  ETDXML.value('/root[1]/PRPMST[1]/DownloadEnvironment[1]', 'varchar(5)') AS DownloadEnvironment
from [upd].[PRPMST]