SQL XML 查询出错：'value()' 需要单独的操作数(或空序列)，但找到 'xdt:anyAtomicType *' 类型的操作数,该如何解决

SQL XML 查询出错：'value()' 需要单独的操作数(或空序列)，但找到 'xdt:anyAtomicType *' 类型的操作数
测试数据：

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('(./@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('(./@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)/*消息 2389，级别 16，状态 1，第 4 行XQuery [TEMP.XMLDATA.value()]: 'value()' 需要单独的操作数(或空序列)，但找到 'xdt:anyAtomicType *' 类型的操作数*/ 
 

上面的错误是什么问题导致的？而单单一个XML就没有问题如下：

SQL code

DECLARE @xmlDate xml SELECT @xmlDate = [XMLDATA] FROM TEMPSELECT @xmlDateIF (@xmlDate is not null)BEGIN    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 @xmlDate.nodes('//EX/R') T(C)END

有没有人知道是什么原因？？

[解决办法]
如果定义了schema collection,
..即parent类型为xs:anyType,此时value()返回任何类型即*

反之类型为untypedAtomic,此类型唯一

正确写法:用fn:string()函数

SQL code

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('string(../@EXCHID)[1]','varchar(250)') as 'EXCHID',T.C.value('string(../@DATE)[1]','varchar(250)') as 'DATE',T.C.value('string(../@PERIOD)[1]','varchar(250)') as 'PERIOD' from TEMP cross apply [XMLDATA].nodes('EX/R') T(C) 

 

查看更多 下一篇