SQL SERVER 解析XML字符串

原创已于 2023-03-17 23:39:25 修改 · 2.6k 阅读

4 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#xml #sql

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

SQL Server 笔记专栏收录该内容

11 篇文章

订阅专栏

这段博客内容涉及SQL查询技巧，演示了如何解析XML数据。首先，通过SET选项设置了SQL处理模式，然后声明并初始化了两个XML字符串变量。接着，针对第一个XML字符串，提取了代码和消息值，并通过nodes()函数获取了数据节点下的lb值。对于第二个XML字符串，解析了包含多个用户信息的数据，提取了每个用户的姓名、性别、出生日期、年龄和余额信息。整个过程展示了SQL在处理XML数据时的能力。

--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)

查询效果: