SQL SERVER 解析XML字符串

--SET NOCOUNT ON
--SET ARITHABORT ON 
--SET QUOTED_IDENTIFIER ON  
--SET ANSI_PADDING ON	



DECLARE 
	
	@xml1 VARCHAr(500)= ''+ CHAr(13)
		+ '7'+ CHAr(13)
		+ '校验失败'+ CHAr(13)
		+ ''+ CHAr(13)
		+ '6'+ CHAr(13)
		+ ''+ CHAr(13)
		+ '',

	
	@xml2 VARCHAr(500)= ''+ CHAr(13)
		+ '7'+ CHAr(13)
		+ ''+ CHAr(13)
			+ ''
				+ '张三'
				+ '男'
				+ '2001-01-01'
				+ '20'
				+ '112.6'
			+ ''
			+ ''
				+ '李四'
				+ '女'
				+ '2011-01-02'
				+ '10'
				+ '0.0'
			+ ''
			+ ''
				+ '王五'
				+ '男'
				+ '2016-01-03'
				+ '5'
				+ '17.8'
			+ ''
		+ ''+ CHAr(13)
		+ ''

DECLARE 
	@RootXml XML,
	@ChildXml XML,
	@XmlTable XML

--解析xml1
SET @RootXml= @xml1
SELECt 
	c.value('(code/text())[1]','INT') code,
	c.value('(message/text())[1]','VARCHAr(100)') message
FROM @RootXml.nodes('/xml') as T(c)

SELECt c.value('(lb/text())[1]','INT') lb FROM @RootXml.nodes('/xml/data') as T(c)

SET @ChildXml = @RootXml.query('/xml/data')
SELECt c.value('(lb/text())[1]','INT') lb from @ChildXml.nodes('/data') as T(c)

--解析xml2
SET @RootXml= @xml2
SET @XmlTable = @RootXml.query('/xml/data') 

SELECt 
	c.value('(name/text())[1]','VARCHAr(50)') name,
	c.value('(sex/text())[1]','VARCHAr(2)') sex,
	c.value('(birth/text())[1]','Date') birth,
	c.value('(age/text())[1]','INT') birth,
	c.value('(balance/text())[1]','NUMERIC(12,2)') balance
FROM @XmlTable.nodes('/data/item') as T(c)

查询效果: