无题

DECLARE @x XML

SELECT @x = '

<ROW>

<FIELD NAME="RegisteredId" VALUE="2049070303116"/>

<FIELD NAME="SectionId" VALUE="A070101"/>

<FIELD NAME="SectionName" VALUE="皮肤科门诊"/>

<FIELD NAME="DoctorId" VALUE="0771"/>

<FIELD NAME="DoctorName" VALUE="王曙霞"/>

<FIELD NAME="DateInfo" VALUE="20171027"/>

<FIELD NAME="StartTime" VALUE="10:55"/>

<FIELD NAME="TimeId" VALUE="45677591"/>

<FIELD NAME="DictName" VALUE="等待就诊"/>

<FIELD NAME="PatientsCardNo" VALUE=""/>

<FIELD NAME="PatientsName" VALUE="张洋"/>

<FIELD NAME="PatientsPhone" VALUE="18632133130"/>

<FIELD NAME="PatientsIDCardNo" VALUE="130184198811015723"/>

<FIELD NAME="PatientsGender" VALUE="2"/>

<FIELD NAME="PatientsAge" VALUE=""/>

</ROW>'

    

    --SELECT 

    --@x.query('/ROW/FIELD[@NAME="PatientsName"]') AS PatientsName

    --,@x.query('/ROW/FIELD[@NAME="PatientsPhone"]') AS PatientsPhone

    --,@x.query('/ROW/FIELD[@NAME="PatientsIDCardNo"]') AS PatientsIDCardNo,

    --@x.query('/ROW/FIELD[@NAME="PatientsCardNo"]') AS PatientsCardNo

    

    select 

    @x.value('(/ROW/FIELD[@NAME="PatientsName"]/@VALUE)[1]','varchar(100)') AS '患者姓名'

    ,@x.value('(/ROW/FIELD[@NAME="PatientsIDCardNo"]/@VALUE)[1]','varchar(100)') AS '身份证号'

    ,@x.value('(/ROW/FIELD[@NAME="PatientsPhone"]/@VALUE)[1]','varchar(100)') AS '电话号码'

    ,@x.value('(/ROW/FIELD[@NAME="PatientsCardNo"]/@VALUE)[1]','varchar(20)') AS '卡号'

 

    SQL对Xml字段的操作 - Qi Fei - 博客园  http://www.cnblogs.com/youring2/archive/2008/11/27/1342288.html

 

 

    SQL SERVER XML 学习总结 - 星星之火116 - 博客园  http://www.cnblogs.com/Olive116/p/3355840.html