Sql语句查询XML - 小结

--两种方式查询
DECLARE @varXML    XML,
        @varXML1   XML,
        @varXML2   XML

        
--1、xml数据源为属性方式
SET @varXML = '<PARAM>
                 <Row FID = "1" FName = "张三" />
                 <Row FID = "2" FName = "李四" />
                 <Row FID = "3" FName = "王五" />
               </PARAM>'
                       
Select  T.C.value('@FID','INT')           AS FID,
        T.C.value('@FName','VARCHAR(20)') AS FName
From    @varXML.nodes('/PARAM/Row') T(C)

--2、xml数据源为标签方式
SET @varXML1 = '<PARAM>
                 <Row><FID>1</FID><FName>张三</FName></Row>
                 <Row><FID>2</FID><FName>李四</FName></Row>
                 <Row><FID>3</FID><FName>王五</FName></Row>
               </PARAM>'
 
Select  T.C.value('(FID)[1]','INT')           AS FID,
        T.C.value('(FName)[1]','VARCHAR(20)') AS FName
From    @varXML1.nodes('/PARAM/Row') T(C)      

--3、xml数据源为属性方式(多级嵌套)
SET @varXML2 = '<root>
                  <hosptail hospCode = "1" hospName = "测试医院" >
                    <dept deptCode = "2" deptName = "内科" >
                      <doctor docCode = "3" docName = "王五" ></doctor>
                    </dept>
                  </hosptail>
                </root>'

Select T.C.value('(/root/hosptail/@hospCode)[1]','INT') AS FID,
       T.C.value('(/root/hosptail/@hospName)[1]','VARCHAR(20)') AS FName,
       T.C.value('(/root/hosptail/dept/@deptCode)[1]','INT') AS FID,
       T.C.value('(/root/hosptail/dept/@deptName)[1]','VARCHAR(20)') AS FName,
       T.C.value('(/root/hosptail/dept/doctor/@docCode)[1]','INT') AS FID,
       T.C.value('(/root/hosptail/dept/doctor/@docName)[1]','VARCHAR(20)') AS FName
From   @varXML2.nodes('/root') T(C)

 

应用场景：对外提供的WebService，别人访问接口后，传入XML参数，直接将XML当做存储过程的参数，在存储过程中查询、游标等解析处理。

 

当入参同一个参数 有多个时，

--入参中 同一个参数有多个的情况，如下<detailId>LYTSA0000942|2</detailId> <detailId>LYTSA0000942|1</detailId>

declare @xmlStr XML 

set @xmlStr = '<root><medicalCardNumber>001000030044374</medicalCardNumber><outTradeNo>ZJZF_DZSYCSFYBJY_10391149</outTradeNo><amount>0.00</amount>
                             <payTime>2020-04-17 08:50:01</payTime><payType>WeChatPay</payType><balancePayment>0.07</balancePayment><hospitalId>0534_0115</hospitalId>
                             <detailId>LYTSA0000942|2</detailId><detailId>LYTSA0000942|1</detailId>
                       </root>'

--通过指定(detailId)[1]  或  (detailId)[2]  来确定取哪个 参数
Select  T.C.value('(medicalCardNumber)[1]','varchar(1024)')  AS FID,
        T.C.value('(outTradeNo)[1]','VARCHAR(1024)')         AS FName,
        T.C.value('(detailId)[1]','varchar(1024)')           AS detailId1,
        T.C.value('(detailId)[2]','varchar(1024)')           AS detailId2
From    @xmlStr.nodes('/root') T(C)     

 

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

declare @xmlStr XML   set @xmlStr = '<root> <recipleList> <a>a|2</a> <b>b|1</b> </recipleList> <recipleList> <a>a|4</a> <b>b|3</b> </recipleList> <recipleList> <a>a|4</a> <b>b|3</b> </recipleList> </root>'   --取数组列表字段数据 Select T.C.value('(a)[1]','varchar(1024)') AS detailId1, T.C.value('(b)[1]','varchar(1024)') AS detailId1 From @xmlStr.nodes('/root/recipleList') T(C)     --取第一行数据 Select T.C.value('(recipleList/a)[1]','varchar(1024)') AS detailId1, T.C.value('(recipleList/b)[1]','varchar(1024)') AS detailId1 From @xmlStr.nodes('/root') T(C)