sql for xml query sample

原创

jwsoft007 2022-08-19 11:17:14 博主文章分类：sql for xml ©著作权

文章标签 xml 5e ide 文章分类 后端开发

©著作权归作者所有：来自51CTO博客作者jwsoft007的原创作品，请联系作者获取转载授权，否则将追究法律责任

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  @GeographyTierKey_Province=@x.value('(Audits/AuditItem/DataXml/PatientProfile/PatientLocation/GeographyTierKey_Province)[0]','varchar(100)')
     
  --   print @GeographyTierKey_Province

  declare @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