我们经常在SQL Server列中存一些XML来作为配置文件或者是保存特殊信息,那么如何将其展开并查询它或将其呈现为关系数据? 其实在T-SQL 下可以很容易的实现。
示例xml
<catalog> <book id="bk101"> <author>Gambardella, Matthew</author> <title>XML Developer's Guide</title> <genre>Computer</genre> <price>44.95</price> <publish_date>2000-10-01</publish_date> </book> <book id="bk102"> <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <price>5.95</price> <publish_date>2000-12-16</publish_date> </book> </catalog>
我们先把xml插入到一个临时表中,只有两个字段ConfigName和ConfigXML
create table #config ( ConfigName varchar(100), ConfigXML xml ) insert into #config select 'TestConfig', '<catalog> <book id="bk101"> <author>Gambardella, Matthew</author> <title>XML Developer''s Guide</title> <genre>Computer</genre> <price>44.95</price> <publish_date>2000-10-01</publish_date> </book> <book id="bk102"> <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <price>5.95</price> <publish_date>2000-12-16</publish_date> </book> </catalog>'
好的,下面来看一下场景是如何用sql 实现的。
- 获取所有xml 中各个book的子xml内容, 这种情况出现在为前台系统提供xml片段。
查询语句为
select r.value('@id','varchar(50)') as bookid, config.r.query('.') as xmlconfig from #config cross apply ConfigXML.nodes('/catalog/book') as config(r) where ConfigName = 'TestConfig'
- 将各个节点转换为关系数据库表结构
查询语句为
select config.r.value('@id', 'varchar(50)') AS BookID, config.r.value('(author/text())[1]', 'varchar(50)') AS author, config.r.value('(title/text())[1]', 'varchar(50)') AS title, config.r.value('(genre/text())[1]', 'varchar(50)') AS genre, config.r.value('(price/text())[1]', 'varchar(50)') AS price, config.r.value('(publish_date/text())[1]', 'varchar(50)') AS publish_date from #config cross apply ConfigXML.nodes('/catalog/book') as config(r) where ConfigName = 'TestConfig'
查询 bookid为k101的信息
方式1: 直接在2的结果集中加上一个where条件and config.r.value('@id','varchar(50)') = 'bk101'
select config.r.value('@id', 'varchar(50)') AS BookID, config.r.value('(author/text())[1]', 'varchar(50)') AS author, config.r.value('(title/text())[1]', 'varchar(50)') AS title, config.r.value('(genre/text())[1]', 'varchar(50)') AS genre, config.r.value('(price/text())[1]', 'varchar(50)') AS price, config.r.value('(publish_date/text())[1]', 'varchar(50)') AS publish_date from #config cross apply ConfigXML.nodes('/catalog/book') as config(r) where ConfigName = 'TestConfig' and config.r.value('@id','varchar(50)') = 'bk101'
方式2:修改cross apply的xml path
select config.r.value('@id', 'varchar(50)') AS BookID, config.r.value('(author/text())[1]', 'varchar(50)') AS author, config.r.value('(title/text())[1]', 'varchar(50)') AS title, config.r.value('(genre/text())[1]', 'varchar(50)') AS genre, config.r.value('(price/text())[1]', 'varchar(50)') AS price, config.r.value('(publish_date/text())[1]', 'varchar(50)') AS publish_date from #config cross apply ConfigXML.nodes('/catalog/book[@id=''bk101'']') as config(r) where ConfigName = 'TestConfig'
注:如果我们从sql server参数里面拼接xpath需要添加sql:variable来表示他是一个sql server变量而不是xml属性名。
declare @bookid varchar(30) = 'bk101' select config.r.value('@id', 'varchar(50)') AS BookID, config.r.value('(author/text())[1]', 'varchar(50)') AS author, config.r.value('(title/text())[1]', 'varchar(50)') AS title, config.r.value('(genre/text())[1]', 'varchar(50)') AS genre, config.r.value('(price/text())[1]', 'varchar(50)') AS price, config.r.value('(publish_date/text())[1]', 'varchar(50)') AS publish_date from #config cross apply ConfigXML.nodes('/catalog/book[@id=sql:variable("@bookid")]') as config(r) where ConfigName = 'TestConfig'
4. 查询每个book 的id和author信息
方式1: 使用第二步结果集
select config.r.value('@id', 'varchar(50)') AS BookID, config.r.value('(author/text())[1]', 'varchar(50)') AS author from #config cross apply ConfigXML.nodes('/catalog/book') as config(r) where ConfigName = 'TestConfig'
方式2:
select config.r.value('../@id', 'varchar(50)') AS BookID, config.r.value('.', 'varchar(50)') AS author from #config cross apply ConfigXML.nodes('/catalog/book/author') as config(r) where ConfigName = 'TestConfig'