create XML SCHEMA COLLECTION TESTScheam as N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="EX">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="R" minOccurs="0" maxOccurs="unbounded">
                    <xsd:complexType>
                        <xsd:sequence />
                        <xsd:attribute name="x1" type="xsd:int" />
                        <xsd:attribute name="x2" type="xsd:int" />
                        <xsd:attribute name="x3" type="xsd:int" />
                        <xsd:attribute name="x4" type="xsd:int" />
                        <xsd:attribute name="x5" type="xsd:int" />
                        <xsd:attribute name="x6" type="xsd:int" />
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
            <xsd:attribute name="DATE" type="xsd:string" />
            <xsd:attribute name="PERIOD" type="xsd:string" />
            <xsd:attribute name="EXCHID" type="xsd:string" />
        </xsd:complexType>
    </xsd:element>
</xsd:schema>'
CREATE TABLE TEMP ([ID] INT IDENTITY(1,1),[XMLDATA] XML(TESTScheam))

INSERT INTO TEMP select 
'<EX DATE="101221" PERIOD="00000100" EXCHID="STM09B2R12">
  <R x1="1" x2="3600" x3="0" x6="0" />
  <R x1="1" x2="3600" x3="0" x6="0" />
  <R x1="1" x2="3600" x3="0" x6="0" />
</EX>'
union all select
'<EX DATE="101221" PERIOD="01000200" EXCHID="STM09B2R12">
  <R x1="1" x2="3600" x3="0" x5="0" x6="0" />
  <R x1="1" x2="3600" x3="0" x5="0" x6="0" />
  <R x1="1" x2="3600" x3="0" x5="0" x6="0" />
  <R x1="1" x2="3600" x3="0" x5="0" x6="0" />
</EX>'
union all select 
'<EX DATE="101221" PERIOD="02000300" EXCHID="STM09B2R12">
  <R x1="1" x2="0" x3="0" x4="0" x5="0" x6="0" />
  <R x1="1" x2="0" x3="0" x4="0" x5="0" x6="0" />
  <R x1="1" x2="0" x3="0" x4="0" x5="0" x6="0" />
  <R x1="1" x2="0" x3="0" x4="0" x5="0" x6="0" />
  <R x1="1" x2="0" x3="0" x4="0" x5="0" x6="0" />
</EX>'

--下面这句就可以执行
select T.C.value('(./@x1)[1]','varchar(250)') as 'ASLDUR',T.C.value('(./@x2)[1]','varchar(250)') as 'CHOVERS',
T.C.value('(./@x3)[1]','varchar(250)') as 'NSIFTR',T.C.value('(./@x4)[1]','varchar(250)') as 'SYS7IND',
T.C.value('(./@x5)[1]','varchar(250)') as 'LBUSDUR',T.C.value('(./@x6)[1]','varchar(250)') as 'LINHNO'
--,T.C.value('(../@EXCHID)[1]','varchar(250)') as 'EXCHID'
--,T.C.value('(../@DATE)[1]','varchar(250)') as 'DATE',
--T.C.value('(../@PERIOD)[1]','varchar(250)') as 'PERIOD' 
from TEMP cross apply [XMLDATA].nodes('EX/R') T(C)

--去掉注析后就不行
select T.C.value('(./@x1)[1]','varchar(250)') as 'ASLDUR',T.C.value(