27,579
社区成员
发帖
与我相关
我的任务
分享
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 *' 类型的操作数
*/
DECLARE @xmlDate xml
SELECT @xmlDate = [XMLDATA] FROM TEMP
SELECT @xmlDate
IF (@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
CREATE TABLE TEMP ([ID] INT IDENTITY(1,1),[XMLDATA] XML)
select a.* from TEMP cross apply(
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 [XMLDATA].nodes('EX/R') T(C)
) as a
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)
select
T.C.value('(/@EXCHID)[1]','varchar(250)') as 'EXCHID'
from TEMP cross apply [XMLDATA].nodes('EX/R') T(C)
--消息 2390,级别 16,状态 1,第 2 行
--XQuery [TEMP.XMLDATA.value()]: 不支持顶级 attribute 节点
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'
,S.C.value('(./@EXCHID)[1]','varchar(250)') as 'EXCHID'
,S.C.value('(./@DATE)[1]','varchar(250)') as 'DATE',
S.C.value('(./@PERIOD)[1]','varchar(250)') as 'PERIOD'
from TEMP cross apply [XMLDATA].nodes('EX/R') T(C)
cross apply [XMLDATA].nodes('EX') S(C)
不懂,帮顶,学习,蹭分.