使用xquery从xml中提取数据的最佳方法 [英] The best way to extract data from xml with xquery
问题描述
考虑以下 xml:
<Persons num="3">
<Person age="5" />
<Person age="19" />
</Persons>
有必要把这个xml提取成关系表:
There is a need to extract this xml into a relational table:
Persons table (Age1 int, Age2 int, Age3 int , Age4 int)
解析必须满足以下约束:
Parsing has to satisfy the following constraints:
- 必须将年龄 >=18 的所有人员分配到列号最小的列中,并且值必须为 18
- 如果没有给出此人的年龄,则等于 18 岁
- 所有年龄小于 18 岁的人都必须遵守
- 如果人数少于 4 人,未提供的必须年龄=-1
在给定的示例中,有 3 个人,提供了其中 2 个人的年龄:分别为 5 岁和 19 岁.Persons 表的内容必须如下:
In a given example, there are 3 persons, ages of 2 of them are provided: 5 and 19 respectively. The content of the table Persons has to be the following:
18 18 5 -1
使用 xpath 是否有最好的方法?
Is there the best way to do so with xpath?
到目前为止,我可以解析 xml 并分配年龄,但不清楚如何进行排序:
Till now I can parse the xml and assign ages but what is not clear is to how make ordering:
declare @XmlData xml =
'<Persons num="3">
<Person age="5" />
<Person age="19" />
</Persons>'
declare @Persons table (Age1 int, Age2 int, Age3 int , Age4 int)
insert into @Persons (Age1, Age2, Age3, Age4)
select ISNULL(Age1, case when Num>= 1 then 18 else -1 end) Age1
, ISNULL(Age2, case when Num>= 2 then 18 else -1 end) Age2
, ISNULL(Age3, case when Num>= 3 then 18 else -1 end) Age3
, ISNULL(Age4, case when Num>= 4 then 18 else -1 end) Age4
from (
select Persons.Person.value('@num','smallint') as Num
,Persons.Person.value('Person[@age<18][1]/@age','smallint') as Age1
,Persons.Person.value('Person[@age<18][2]/@age','smallint') as Age2
,Persons.Person.value('Person[@age<18][3]/@age','smallint') as Age3
,Persons.Person.value('Person[@age<18][4]/@age','smallint') as Age4
from @XmlData.nodes('/Persons') Persons(Person)
) Persons
select *
from @Persons
结果是
5 18 18 -1
推荐答案
另一种解决方案需要更多的 sql 代码,但在估计的执行计划中仅花费约 80.
Another solution requires a bit more sql code but costs only ~80 in estimated execution plan.
问题陈述有一个约束条件:Persons/@num 必须等于 Person 标签的数量
There is one constraint wrt the problem statement: Persons/@num has to be equal to a number of Person tags
限制是:
- 每个房间的人数有限
这里是sql代码:
--//initial xml data
declare @XmlData xml =
'<Persons roomid="1" num="3">
<Person age="19" />
<Person age="10" />
<Person age="5" />
</Persons>
<Persons roomid="4" num="4">
<Person age="17" />
<Person age="10" />
<Person age="5" />
<Person age="1" />
</Persons>'
--//shade xml into temporal table: rank is applied to an age in descreasing order
declare @tmp table (age int, roomid int, orderid int)
insert into @tmp(age,roomid,orderid)
select Persons.age
,Persons.roomid
,ROW_NUMBER () over (partition by Persons.roomid order by Persons.age desc)
from(
select Ps.P.value('(@age)[1]','smallint') age
,Ps.P.value('(../@roomid)[1]','smallint') roomid
from @XmlData.nodes('/Persons/Person') Ps(P)
)Persons
order by Persons.roomid,Persons.age desc
--//provide ordering for roomid: since roomid may be different (the only thing that is required that roomid is unique)
declare @roomidmapping table (roomid int, roomorderid int)
insert into @roomidmapping(roomid, roomorderid)
select roomid, ROW_NUMBER () over (order by roomid asc)
from @tmp
group by roomid
declare @roomnumber int = @@ROWCOUNT
--//final result
;WITH ConsequtiveNums AS
(
SELECT 1 AS Number
UNION ALL
SELECT Number+1
FROM ConsequtiveNums
WHERE Number<@roomnumber
)
select (select case when age>18 then 18 else age end from @tmp T inner join @roomidmapping M on T.roomid = M.roomid where T.orderid = 1 and M.roomorderid = CN.Number)
,(select case when age>18 then 18 else age end from @tmp T inner join @roomidmapping M on T.roomid = M.roomid where T.orderid = 2 and M.roomorderid = CN.Number)
,(select case when age>18 then 18 else age end from @tmp T inner join @roomidmapping M on T.roomid = M.roomid where T.orderid = 3 and M.roomorderid = CN.Number)
,(select case when age>18 then 18 else age end from @tmp T inner join @roomidmapping M on T.roomid = M.roomid where T.orderid = 4 and M.roomorderid = CN.Number)
from ConsequtiveNums CN
这篇关于使用xquery从xml中提取数据的最佳方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!