sql for xml query sample
来源：互联网 发布：linux查看端口是否打开 编辑：程序博客网 时间：2024/04/27 18:53
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)/*EveryMondayEveryThursdayEverySunday*/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--------------------EveryMondayEveryThursdayEverySunday (3 行受影响)*/DECLARE @handel int;EXEC sp_xml_preparedocument @handel output, @xSELECT * from OPENXML(@handel,'/ArrayOfScheduledTime/ScheduledTime',2)WITH(RecurrenceType varchar(50)) EXEC sp_xml_removedocument @handel/**************结果*****************EveryMondayEveryThursdayEverySunday*/
sample2:
DECLARE @xDailyConfig XMLset @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:002000-01-01T13:00:002000-01-01T19:00:00*/DECLARE @handel int;EXEC sp_xml_preparedocument @handel output, @xDailyConfigSELECT * from OPENXML(@handel,'/ArrayOfScheduledTime/ScheduledTime/Stamp/dateTime',3)WITH(dateTime varchar(50) '.[1]') EXEC sp_xml_removedocument @handel/*2000-01-01T09:00:002000-01-01T13:00:002000-01-01T19:00:00*/
-- 统计每个RecurrenceType对其对应的dateTime的个数
declare @x xml select @x='<ArrayOfScheduledTime> <ScheduledTime> <RecurrenceType>EverySaturday</RecurrenceType> <Stamp> <dateTime>2000-01-01T07:00:00</dateTime> <dateTime>2000-01-01T08:00:00</dateTime> </Stamp> </ScheduledTime> <ScheduledTime> <RecurrenceType>EveryWednesday</RecurrenceType> <Stamp> <dateTime>2000-01-01T09:00:00</dateTime> </Stamp> </ScheduledTime> <ScheduledTime> <RecurrenceType>EveryFriday</RecurrenceType> <Stamp> <dateTime>2000-01-01T09:00:00</dateTime> <dateTime>2000-01-01T09:20:00</dateTime> <dateTime>2000-01-01T09:40:00</dateTime> </Stamp> </ScheduledTime> <ScheduledTime> <RecurrenceType>EverySunday</RecurrenceType> <Stamp> <dateTime>2000-01-01T09:00:00</dateTime> </Stamp> </ScheduledTime> </ArrayOfScheduledTime>'SELECT T2.RecurrenceType,COUNT(T3.[dateTime]) [Count]FROM(SELECT CONVERT(XML,N.v.query('.'))C1 FROM @x.nodes('/ArrayOfScheduledTime/ScheduledTime')N(v))T1OUTER APPLY(SELECT M.v.value('.','VARCHAR(100)')RecurrenceType FROM T1.C1.nodes('//RecurrenceType') M(v))T2OUTER APPLY(SELECT L.v.value('.','VARCHAR(100)')[dateTime] FROM T1.C1.nodes('//dateTime') L(v))T3GROUP BY T2.RecurrenceType/*RecurrenceType Count-------------------- -----------EverySaturday 2EveryWednesday 1EveryFriday 3EverySunday 1*/SELECT N.v.query('RecurrenceType').value('.','VARCHAR(20)') RecurrenceType, N.v.query('count(Stamp//dateTime)').value('.','int') [Count]FROM @x.nodes('/ArrayOfScheduledTime/ScheduledTime')N(v)/*RecurrenceType Count-------------------- -----------EverySaturday 2EveryWednesday 1EveryFriday 3EverySunday 1*/ 其它样例:
declare @x xml='<ArrayOfGuid> <guid>754350a3-228e-4981-a430-a5f62af9b936</guid> <guid>792f9404-6330-4302-999a-3ec36e9e1275</guid></ArrayOfGuid>' -- get count SELECT TOP 1 N.v.query('count(//guid)').value('.','varchar(100)') [COUNT]FROM @x.nodes('/ArrayOfGuid/guid')N(v)-- get each guidValueSELECT N.v.value('.','VARCHAR(100)') guidValue--N.v.query('count(//guid)').value('.','VARCHAR(100)') [Count]FROM @x.nodes('/ArrayOfGuid/guid')N(v)/* resultCOUNT----------------------------------------------------------------------------------------------------2(1 行受影响)guidValue----------------------------------------------------------------------------------------------------754350a3-228e-4981-a430-a5f62af9b936792f9404-6330-4302-999a-3ec36e9e1275(2 行受影响)*/other sample 2:
declare @x xml ='<Audits OperatorKey="77de120a-7704-49b1-8980-8f1e9ad65edd"> <AuditItem SourceName="PatientProfile" SourceIdentity="77de120a-7704-49b1-8980-8f1e9ad65edd"> <DataXml> <PatientProfile> <Key p2:nil="true" xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" /> <CreatedStamp>2015-07-28T03:18:46.843Z</CreatedStamp> <LastUpdatedStamp>2015-07-28T03:18:46.844Z</LastUpdatedStamp> <State>Normal</State> <UserKey>77de120a-7704-49b1-8980-8f1e9ad65edd</UserKey> <FirstName>zhang</FirstName> <LastName>andy</LastName> <MiddleName /> <Gender>Male</Gender> <Birthday p2:nil="true" xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" /> <AvatarCode /> <AvatarKey p2:nil="true" xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" /> <ProgramKeyList /> <Overall /> <BirthLocation /> <Residence /> <BodyHeight>0</BodyHeight> <MainDisease /> <OtherDisease> <TimeBasedDiseaseItemOfDiseaseName> <Time>1</Time> <DiseaseItem> <Key>15aabe2b-73af-426d-b668-b93fb84035ec</Key> <CreatedStamp>2015-07-28T03:41:01.1260093Z</CreatedStamp> <LastUpdatedStamp>2015-07-28T03:41:01.1260093Z</LastUpdatedStamp> <State>Normal</State> <ThirdPartyIdentity>542cb968-f806-46ee-9e9d-8ba5bdbf8da7</ThirdPartyIdentity> <IsMain>false</IsMain> <Name /> <CultureInfo>zh-cn</CultureInfo> </DiseaseItem> </TimeBasedDiseaseItemOfDiseaseName> </OtherDisease> <Symptoms /> <MedicalHistory /> <Infections /> <Allergies /> <FamilyHistory /> <SmokingHistory /> <DrinkingHistory /> <EmergencyContact /> <SurgeryHistory> <SurgeryHistory> <Key p4:nil="true" xmlns:p4="http://www.w3.org/2001/XMLSchema-instance" /> <CreatedStamp>2015-07-28T03:18:46.848Z</CreatedStamp> <LastUpdatedStamp>2015-07-28T03:18:46.849Z</LastUpdatedStamp> <State>Normal</State> <SurgeryDate>2015-02-01T03:18:46.849Z</SurgeryDate> <Surgery> <Key>b259b887-ac76-4f01-a03d-afa1a8e238a2</Key> <CreatedStamp>2015-07-28T03:18:41.685Z</CreatedStamp> <LastUpdatedStamp>2015-07-28T03:18:41.688Z</LastUpdatedStamp> <State>Normal</State> <NameCN>肝移植</NameCN> <NameEN>Liver Transplant</NameEN> </Surgery> </SurgeryHistory> </SurgeryHistory> <PatientLocation> <Key p3:nil="true" xmlns:p3="http://www.w3.org/2001/XMLSchema-instance" /> <CreatedStamp>2015-07-28T03:18:46.845Z</CreatedStamp> <LastUpdatedStamp>2015-07-28T03:18:46.847Z</LastUpdatedStamp> <State>Normal</State> <UserKey>77de120a-7704-49b1-8980-8f1e9ad65edd</UserKey> <GeographyTierKey_Province>d4538110-24fc-4edd-9320-1f6b62b192fa</GeographyTierKey_Province> <GeographyTierKey_City>a92c824c-512e-4d8b-812d-448b95546662</GeographyTierKey_City> <Province>吉林省</Province> <City>白城市</City> </PatientLocation> <DrugRemind> <DrugRemind> <Key>db9808b9-4957-47c6-99c1-92ca97548392</Key> <CreatedStamp>2015-07-28T03:13:18.753Z</CreatedStamp> <LastUpdatedStamp>2015-07-28T03:13:18.753Z</LastUpdatedStamp> <State>Normal</State> <TargetUserKey>77de120a-7704-49b1-8980-8f1e9ad65edd</TargetUserKey> <DrugKey>00000000-0000-0000-0000-000000000000</DrugKey> <BrandKey>00000000-0000-0000-0000-000000000000</BrandKey> <DrugName>新山地明/普乐可复</DrugName> <BrandName>新山地明/普乐可复</BrandName> <BoxSize>0</BoxSize> <Unit>;2-6 mg</Unit> <Amount>1</Amount> <DoseStrength>0</DoseStrength> <DoseStrengthWithUnit>2-6 mg</DoseStrengthWithUnit> <DoseTimePin> <Recurrence>EveryHour</Recurrence> <ReferenceStamps> <dateTime>2000-01-01T00:00:00Z</dateTime> <dateTime>2000-01-01T12:00:00Z</dateTime> </ReferenceStamps> <StartStamp>2014-08-22T00:00:00Z</StartStamp> <EndStamp p5:nil="true" xmlns:p5="http://www.w3.org/2001/XMLSchema-instance" /> </DoseTimePin> <CreatedBy>77de120a-7704-49b1-8980-8f1e9ad65edd</CreatedBy> </DrugRemind> <DrugRemind> <Key>bb49ed53-b16f-4caa-882e-82dcdf8ce991</Key> <CreatedStamp>2015-07-28T03:13:18.747Z</CreatedStamp> <LastUpdatedStamp>2015-07-28T03:13:18.747Z</LastUpdatedStamp> <State>Normal</State> <TargetUserKey>77de120a-7704-49b1-8980-8f1e9ad65edd</TargetUserKey> <DrugKey>00000000-0000-0000-0000-000000000000</DrugKey> <BrandKey>00000000-0000-0000-0000-000000000000</BrandKey> <DrugName>米芙/骁悉</DrugName> <BrandName>米芙/骁悉</BrandName> <BoxSize>0</BoxSize> <Unit>;1080 mg</Unit> <Amount>1</Amount> <DoseStrength>0</DoseStrength> <DoseStrengthWithUnit>1080 mg</DoseStrengthWithUnit> <DoseTimePin> <Recurrence>EveryHour</Recurrence> <ReferenceStamps> <dateTime>2000-01-01T00:00:00Z</dateTime> <dateTime>2000-01-01T12:00:00Z</dateTime> </ReferenceStamps> <StartStamp>2014-08-22T00:00:00Z</StartStamp> <EndStamp p5:nil="true" xmlns:p5="http://www.w3.org/2001/XMLSchema-instance" /> </DoseTimePin> <CreatedBy>77de120a-7704-49b1-8980-8f1e9ad65edd</CreatedBy> </DrugRemind> </DrugRemind> </PatientProfile> </DataXml> </AuditItem></Audits>' --declare @GeographyTierKey_Province varchar(100)--select @GeographyTierKey_Province=@x.value('(Audits/AuditItem/DataXml/PatientProfile/PatientLocation/GeographyTierKey_Province)[0]','varchar(100)') -- print @GeographyTierKey_Provincedeclare @GeographyTierKey_Province varchar(100)--select @GeographyTierKey_Province=@x.value('(Audits/AuditItem/DataXml/PatientProfile/PatientLocation/GeographyTierKey_Province)[0]','varchar(100)') --print @GeographyTierKey_Province select @GeographyTierKey_Province=(SELECT top 1 N.v.value('.','VARCHAR(100)') guidValue --N.v.query('count(//guid)').value('.','VARCHAR(100)') [Count] FROM @x.nodes('Audits/AuditItem/DataXml/PatientProfile/PatientLocation/GeographyTierKey_Province')N(v) ) select @GeographyTierKey_Province -- d4538110-24fc-4edd-9320-1f6b62b192fa 来源：
SQL特殊语句的笔记
http://www.2cto.com/database/201205/133329.html
说明，使用 openxml后，一定要记得用 sp_xml_removedocument 释放xml document资源。
(结束)
0 0
- sql for xml query sample
- build.xml sample for ant
- sql Transact for good Sample
- SQL Query XML column
- sample api for xml简单示例
- SQL 2005 xml 处理的一些sample
- mongodb query promise sample
- NStarfaiNet的SQL XML文件示范(maps/sample.xml)
- Simple sample for transforming XML to HTML by using XSLT
- Distributed SQL Query Engine for Big Data
- SQL Query XML column. SQL 查询 ｘｍｌ 字段
- SQL FOR XML
- sql For XML Path
- sql for xml path
- SQL FOR XML PATH
- SQL Server FOR XML
- sql for xml path
- Northwind and pubs Sample Databases for SQL Server 2000
- wince启动增加进度条
- 难友彭旭受脑控武器迫害的经过
- Project 3 Hashing
- 探索Scala（4）-- Case Classes
- Qt 程序里播放 flash(控件定时关闭)
- sql for xml query sample
- imagebutton按下效果（不用selector）
- android 工具类篇 DeviceUtil
- 行为6
- 自定义 ViewGroup 支持无限循环翻页之二(处理触摸事件)
- ProgressBar颜色
- 害人者究竟是谁？
- android 工具类篇 LogUtil
- latex beamer 制作ppt，包括动画效果。插入gif图
