SQL SERVER 解析XML字符串

ceaningking

已于 2023-03-17 23:39:25 修改

阅读量2.2k

点赞数

分类专栏： SQL Server 笔记文章标签： xml sql

于 2021-12-11 22:09:48 首次发布

本文链接：https://blog.csdn.net/ceaningking/article/details/121880389

版权

SQL Server 笔记专栏收录该内容

11 篇文章 0 订阅

订阅专栏

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



DECLARE 
	/*
		<xml>
			<code>9</code>
			<message>校验失败</message>
			<data>
				<lb>6</lb>
			</data>
		</xml>
	*/
	@xml1 VARCHAR(500)= '<xml>'+ CHAR(13)
		+ '<code>7</code>'+ CHAR(13)
		+ '<message>校验失败</message>'+ CHAR(13)
		+ '<data>'+ CHAR(13)
		+ '<lb>6</lb>'+ CHAR(13)
		+ '</data>'+ CHAR(13)
		+ '</xml>',

	/*
		<xml>
			<code>7</code>
			<data>
				<item>
					<name>张三</name>
					<sex>男</sex>
					<birth>2001-01-01</birth>
					<age>20</age>
					<balance>112.6</balance>
				</item>
				<item>
					<name>李四</name>
					<sex>女</sex>
					<birth>2011-01-02</birth>
					<age>10</age>
					<balance>0.0</balance>
				</item>
				<item>
					<name>王五</name>
					<sex>男</sex>
					<birth>2016-01-03</birth>
					<age>5</age>
					<balance>17.8</balance>
				</item>
			</data>
		</xml>
	*/
	@xml2 VARCHAR(500)= '<xml>'+ CHAR(13)
		+ '<code>7</code>'+ CHAR(13)
		+ '<data>'+ CHAR(13)
			+ '<item>'
				+ '<name>张三</name>'
				+ '<sex>男</sex>'
				+ '<birth>2001-01-01</birth>'
				+ '<age>20</age>'
				+ '<balance>112.6</balance>'
			+ '</item>'
			+ '<item>'
				+ '<name>李四</name>'
				+ '<sex>女</sex>'
				+ '<birth>2011-01-02</birth>'
				+ '<age>10</age>'
				+ '<balance>0.0</balance>'
			+ '</item>'
			+ '<item>'
				+ '<name>王五</name>'
				+ '<sex>男</sex>'
				+ '<birth>2016-01-03</birth>'
				+ '<age>5</age>'
				+ '<balance>17.8</balance>'
			+ '</item>'
		+ '</data>'+ CHAR(13)
		+ '</xml>'

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)

查询效果: