Field needs to be parsed in SQL that looks like xml

I have a field in a sql table that looks like xml. I need to parse it out to get the field. It looks like :

<?xml version="1.0" encoding="utf-8"?>
<Vendor>
  <Vendor_ID><![CDATA[2 RIVERS CONTRA]开发者_C百科]></Vendor_ID>
  <Vendor_Name><![CDATA[2 RIVERS CONTRACTING & SERVICES, INC.]]></Vendor_Name>
  <Vendor_Contact><![CDATA[]]></Vendor_Contact>
  <Phone><![CDATA[0                    ]]></Phone>
  <Address><![CDATA[P.O. BOX 1512  ]]></Address>
  <City><![CDATA[ARLINGTON]]></City>
  <State><![CDATA[MA]]></State>
  <Country><![CDATA[]]></Country>
</Vendor>

I need to pull out such fields as CDATA[2 RIVERS CONTRA

Thanks for the help in advance.

Not only does it looks like XML it is XML.

If the column in your table is an XML data type column you can get the value like this.

select XMLCol.value('(/Vendor/Vendor_ID)[1]', 'varchar(50)') as Vendor_ID
from YourTable 

If it is a varchar/nvarchar column you need to first cast to XML.

select cast(XMLCol as xml).value('(/Vendor/Vendor_ID)[1]', 'varchar(50)') as Vendor_ID
from YourTable 

Working sample:

declare @YourTable table (XMLCol varchar(max))

insert into @YourTable values
('<?xml version="1.0" encoding="utf-8"?>
  <Vendor>
    <Vendor_ID><![CDATA[2 RIVERS CONTRA]]></Vendor_ID>
    <Vendor_Name><![CDATA[2 RIVERS CONTRACTING & SERVICES, INC.]]></Vendor_Name>
    <Vendor_Contact><![CDATA[]]></Vendor_Contact>
    <Phone><![CDATA[0                    ]]></Phone>
    <Address><![CDATA[P.O. BOX 1512  ]]></Address>
    <City><![CDATA[ARLINGTON]]></City>
    <State><![CDATA[MA]]></State>
    <Country><![CDATA[]]></Country>
  </Vendor>')

select cast(XMLCol as xml).value('(/Vendor/Vendor_ID)[1]', 'varchar(50)') as Vendor_ID
from @YourTable 

Result:

Vendor_ID
----------------
2 RIVERS CONTRA