--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)
查询效果: