首页 > 代码库 > sql for xml query sample
sql for xml query sample
sample 1:
declare @x xml select @x='<ArrayOfScheduledTime> <ScheduledTime> <RecurrenceType>EveryMonday</RecurrenceType> <Stamp> <dateTime>2000-01-01T19:30:00</dateTime> </Stamp> </ScheduledTime> <ScheduledTime> <RecurrenceType>EveryThursday</RecurrenceType> <Stamp> <dateTime>2000-01-01T19:30:00</dateTime> </Stamp> </ScheduledTime> <ScheduledTime> <RecurrenceType>EverySunday</RecurrenceType> <Stamp> <dateTime>2000-01-01T19:30:00</dateTime> </Stamp> </ScheduledTime> </ArrayOfScheduledTime>' SELECT N.v.value('.' , 'VARCHAR(100)')B FROM @x.nodes('/ArrayOfScheduledTime/ScheduledTime/RecurrenceType')N(v) /* EveryMonday EveryThursday EverySunday */
select @x.query('/ArrayOfScheduledTime/ScheduledTime/RecurrenceType') /* <RecurrenceType>EveryMonday</RecurrenceType> <RecurrenceType>EveryThursday</RecurrenceType> <RecurrenceType>EverySunday</RecurrenceType> */
select o.value('RecurrenceType[1]','varchar(20)') 'RecurrenceType' from (select @x 'x') t cross apply x.nodes('/ArrayOfScheduledTime/ScheduledTime') x(o) /* RecurrenceType -------------------- EveryMonday EveryThursday EverySunday (3 行受影响) */
DECLARE @handel int; EXEC sp_xml_preparedocument @handel output, @x SELECT * from OPENXML(@handel,'/ArrayOfScheduledTime/ScheduledTime',2) WITH(RecurrenceType varchar(50)) /**************结果***************** EveryMonday EveryThursday EverySunday */
sample2:
DECLARE @xDailyConfig XML set @xDailyConfig='<ArrayOfScheduledTime> <ScheduledTime> <RecurrenceType>Everyday</RecurrenceType> <Stamp> <dateTime>2000-01-01T09:00:00</dateTime> <dateTime>2000-01-01T13:00:00</dateTime> <dateTime>2000-01-01T19:00:00</dateTime> </Stamp> </ScheduledTime> </ArrayOfScheduledTime>' SELECT N.v.value('.' , 'VARCHAR(100)')B FROM @xDailyConfig.nodes('/ArrayOfScheduledTime/ScheduledTime/Stamp/dateTime')N(v) /* 2000-01-01T09:00:00 2000-01-01T13:00:00 2000-01-01T19:00:00 */
sql for xml query sample
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。