使用XQuery来提取XML数据
问题描述:
考虑下面的XML的最佳方式:使用XQuery来提取XML数据
<Persons num="3">
<Person age="5" />
<Person age="19" />
</Persons>
有必要提取该XML到关系表:
Persons table (Age1 int, Age2 int, Age3 int , Age4 int)
解析必须满足以下约束条件:
- 所有年龄大于等于18岁的人都必须分配到列号最小的列,值必须是18
- 如果不给的人的年龄就等于18
- 随着年龄的所有人< 18必须遵循
- 如果有少于4人,那些不提供必须有年龄= -1
在给定的例子中,有3人,其中2人的年龄分别为5人和19人。表格人员的内容必须如下:
18 18 5 -1
是否有最佳方式使用xpath?
到目前为止我可以解析XML和指定年龄,但有什么不清楚的是如何使排序:
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。
有一个约束WRT问题陈述:人/ @ NUM已经等于了一些人标签
限制是:
- 限制每间房的人数
下面是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
答
我发现有点脏的解决方案:
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('xs:integer(fn:number(@num))+1','int') as Num1
,Persons.Person.value('Person[@age<18][xs:integer(fn:number(../@num))][1]/@age','smallint') as Age1
,Persons.Person.value('Person[@age<18][xs:integer(fn:number(../@num))-1][1]/@age','smallint') as Age2
,Persons.Person.value('Person[@age<18][xs:integer(fn:number(../@num))-2][1]/@age','smallint') as Age3
,Persons.Person.value('Person[@age<18][xs:integer(fn:number(../@num))-3][1]/@age','smallint') as Age4
from @XmlData.nodes('/Persons') Persons(Person)
) Persons
解决方案的想法是首先提取那些> = 18的联系人,然后提取那些是0 <年龄< 18最后定那些不提供给-1
UPD:尽管该解决方案提供了正确的结果,它的成本是很高的事实:〜1000估计的执行计划
如何将年龄存储为临时表中的行,然后使用数据透视查询(通过排序)将它们作为一行移动到最终表结构中? – Tony 2011-05-20 10:06:47