DECLARE @dataSource XML  
 
SET @dataSource='<?xml version="1.0"?>
<msg>
	<appmsg appid="123456" sdkver="0">
		<title>商家信息详情</title>
		<des>58同城生活助手</des>
		<action />
		<type>33</type>
		<showtype>0</showtype>
		<soundtype>0</soundtype>
		<mediatagname />
		<messageext />
		<messageaction />
		<content />
		<contentattr>0</contentattr>
		<url>https://mp.weixin.qq.com/mp/waerrpage?appid=wxc81edb242dec62d4&amp;type=upgrade&amp;upgradetype=3#wechat_redirect
		</url>
		<lowurl />
		<dataurl />
		<lowdataurl />
		<extinfo />
		<sourceusername>gh_ba8feb5923e8@app</sourceusername>
		<sourcedisplayname>58同城生活助手</sourcedisplayname>
		<statextstr />
		<weappinfo>
			<username><![CDATA[gh_ba8feb5923e8@app]]></username>
			<appid><![CDATA[wxc81edb242dec62d4]]></appid>
			<type>2</type>
			<version>56</version>
			<shareId><![CDATA[0_wxc81edb242dec62d4_1665541820_1539330501_0]]></shareId>
			<appservicetype>0</appservicetype>
		</weappinfo>
	</appmsg>
	<fromusername>xjf903512819</fromusername>
	<scene>0</scene>
	<appinfo>
		<version>1</version>
		<appname></appname>
	</appinfo>
	<commenturl></commenturl>
</msg>
'

解析xml

select  @dataSource.value('(/msg/appmsg/@appid)[1]','nvarchar(20)')
select @dataSource.value('(/msg/appmsg/type)[1]','int')
select @dataSource.value('(/msg/appmsg/sourcedisplayname)[1]','nvarchar(20)')