Выборка из XML

declare @x xml = N'<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msxmla="http://schemas.microsoft.com/analysisservices/2003/xmla">
    <xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">
      <xsd:element name="root">
        <xsd:complexType>
          <xsd:sequence minOccurs="0" maxOccurs="unbounded">
            <xsd:element name="row" type="row" />
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
      <xsd:simpleType name="uuid">
        <xsd:restriction base="xsd:string">
          <xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />
        </xsd:restriction>
      </xsd:simpleType>
      <xsd:complexType name="xmlDocument">
        <xsd:sequence>
          <xsd:any />
        </xsd:sequence>
      </xsd:complexType>
      <xsd:complexType name="row">
        <xsd:sequence>
          <xsd:element sql:field="CATALOG_NAME" name="CATALOG_NAME" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="DESCRIPTION" name="DESCRIPTION" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="ROLES" name="ROLES" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="DATE_MODIFIED" name="DATE_MODIFIED" type="xsd:dateTime" minOccurs="0" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:schema>
    <row>
      <CATALOG_NAME>AdventureWorks</CATALOG_NAME>
      <DESCRIPTION />
      <DATE_MODIFIED>2013-12-24T15:57:43.013333</DATE_MODIFIED>
    </row>
    <row>
      <CATALOG_NAME>AdventureWorks 2</CATALOG_NAME>
      <DESCRIPTION />
      <DATE_MODIFIED>2013-12-24T15:57:43.013333</DATE_MODIFIED>
    </row> 
    <row>
      <CATALOG_NAME>AdventureWorks 3</CATALOG_NAME>
      <DESCRIPTION />
      <DATE_MODIFIED>2013-12-24T15:57:43.013333</DATE_MODIFIED>
    </row>           
  </root>
</return>';
 
with xmlnamespaces('urn:schemas-microsoft-com:xml-analysis' as ns1, default 'urn:schemas-microsoft-com:xml-analysis:rowset')
select
 t.n.value('CATALOG_NAME[1]', 'varchar(100)'),
 t.n.value('DATE_MODIFIED[1]', 'datetime')
from
 @x.nodes('/ns1:return/root/row') t(n);

with xmlnamespaces('urn:schemas-microsoft-com:xml-analysis' as ns1, default 'urn:schemas-microsoft-com:xml-analysis:rowset')

select
 t.n.value('*:CATALOG_NAME[1]', 'varchar(100)'),
 t.n.value('*:DATE_MODIFIED[1]', 'datetime')
from
 @x.nodes('/*:return/*:root/*:row') t(n);