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)