测试数据：
- SQL code
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('(.[email protected])[1]','varchar(250)') as 'ASLDUR',T.C.value('(.[email protected])[1]','varchar(250)') as 'CHOVERS',T.C.value('(.[email protected])[1]','varchar(250)') as 'NSIFTR',T.C.value('(.[email protected])[1]','varchar(250)') as 'SYS7IND',T.C.value('(.[email protected])[1]','varchar(250)') as 'LBUSDUR',T.C.value('(.[email protected])[1]','varchar(250)') as 'LINHNO'--,T.C.value('(..[email protected])[1]','varchar(250)') as 'EXCHID'--,T.C.value('(..[email protected])[1]','varchar(250)') as 'DATE',--T.C.value('(..[email protected])[1]','varchar(250)') as 'PERIOD' from TEMP cross apply [XMLDATA].nodes('EX/R') T(C)--去掉注析后就不行select T.C.value('(.[email protected])[1]','varchar(250)') as 'ASLDUR',T.C.value('(.[email protected])[1]','varchar(250)') as 'CHOVERS',T.C.value('(.[email protected])[1]','varchar(250)') as 'NSIFTR',T.C.value('(.[email protected])[1]','varchar(250)') as 'SYS7IND',T.C.value('(.[email protected])[1]','varchar(250)') as 'LBUSDUR',T.C.value('(.[email protected])[1]','varchar(250)') as 'LINHNO',T.C.value('(..[email protected])[1]','varchar(250)') as 'EXCHID',T.C.value('(..[email protected])[1]','varchar(250)') as 'DATE',T.C.value('(..[email protected])[1]','varchar(250)') as 'PERIOD' from TEMP cross apply [XMLDATA].nodes('EX/R') T(C)/*消息 2389，级别 16，状态 1，第 4 行XQuery [TEMP.XMLDATA.value()]: 'value()' 需要单独的操作数(或空序列)，但找到 'xdt:anyAtomicType *' 类型的操作数*/