ML

ML

关注
发信
关注(28)粉丝(399)

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))

EXEC sp_xml_removedocument @handel

/**************结果*****************

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

*/
DECLARE @handel int;

EXEC sp_xml_preparedocument @handel output, @xDailyConfig

SELECT * from OPENXML(@handel,'/ArrayOfScheduledTime/ScheduledTime/Stamp/dateTime',3)

WITH(dateTime varchar(50) '.[1]')

EXEC sp_xml_removedocument @handel

/*

2000-01-01T09:00:00

2000-01-01T13:00:00

2000-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))T1

	OUTER APPLY(

		SELECT M.v.value('.','VARCHAR(100)')RecurrenceType

		FROM T1.C1.nodes('//RecurrenceType') M(v)

	)T2

	OUTER APPLY(

		SELECT L.v.value('.','VARCHAR(100)')[dateTime]

		FROM T1.C1.nodes('//dateTime') L(v)

	)T3

GROUP BY T2.RecurrenceType

/*

RecurrenceType       Count

-------------------- -----------

EverySaturday        2

EveryWednesday       1

EveryFriday          3

EverySunday          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        2

EveryWednesday       1

EveryFriday          3

EverySunday          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 guidValue

	SELECT N.v.value('.','VARCHAR(100)') guidValue

		--N.v.query('count(//guid)').value('.','VARCHAR(100)') [Count]

	FROM @x.nodes('/ArrayOfGuid/guid')N(v)

	/* result

	COUNT

	----------------------------------------------------------------------------------------------------

	2

	(1 行受影响)

	guidValue

	----------------------------------------------------------------------------------------------------

	754350a3-228e-4981-a430-a5f62af9b936

	792f9404-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  @[email protected]('(Audits/AuditItem/DataXml/PatientProfile/PatientLocation/GeographyTierKey_Province)[0]','varchar(100)')

	--	 print @GeographyTierKey_Province

	declare @GeographyTierKey_Province varchar(100)

		--select  @[email protected]('(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资源。

(结束)

05-23 04:40
Powered by LMLPHP ©2024 ML 0.054327