sql:xml读取
本帖最后由 lfz860110 于 2013-05-24 14:09:14 编辑 表A,字段rule是xml类型 ，questionCondition 标签中的id是另外一张表B的主键
表B如下
ID value
Q1 10001
Q2 20001
Q3 30001
Q4 40001
表A记录为：
id name rule
1 ddd 下面xml
xml如下：
<legendRule>
<questionRule>
<questionCondition id="Q1">
<answer>A</answer>
<answer>B</answer>
</questionCondition >
<questionCondition id="Q2">
<answer>C</answer>
<answer>D</answer>
</questionCondition>
<questionCondition id="Q3">
<answer>B</answer>
<answer>C</answer>
</questionCondition>
<questionCondition id="Q4">
<answer>A</answer>
<answer>C</answer>
</questionCondition>
</questionRule>
</legendRule>
我现在要如何得到一个字符串为 "10001:A、B; 20001:C、D; 30001: B、C; 40001:A、C"
最终我要获得数据集为
id name rule
1 ddd "10001:A、B; 20001:C、D; 30001: B、C; 40001:A、C"
求SQL语句 SQL XML 行业数据
[解决办法]
create table 表A
(id int, name varchar(5), [rule] xml)
insert into 表A(id,name,[rule])
select 1, 'ddd',
'<legendRule>
<questionRule>
<questionCondition id="Q1">
<answer>A</answer>
<answer>B</answer>
</questionCondition>
<questionCondition id="Q2">
<answer>C</answer>
<answer>D</answer>
</questionCondition>
<questionCondition id="Q3">
<answer>B</answer>
<answer>C</answer>
</questionCondition>
<questionCondition id="Q4">
<answer>A</answer>
<answer>C</answer>
</questionCondition>
</questionRule>
</legendRule>'
with t1 as
(select a.id,
o.value('../@id','varchar(5)') 'ids',
o.value('.','varchar(5)') 'ans'
from 表A a
cross apply [rule].nodes('/legendRule/questionRule/questionCondition/answer') x(o)
),t2 as
(select a.id,
a.ids+':'+stuff((select '、'+b.ans from t1 b
where b.id=a.id and a.ids=b.ids
for xml path('')),1,1,'') 'ids2'
from t1 a
group by a.id,a.ids
),t3 as
(select a.id,
stuff((select ';'+b.ids2 from t2 b where b.id=a.id for xml path('')),1,1,'') 'rule'
from t2 a
group by a.id
)
select a.id,
a.name,
b.[rule]
from 表A a
left join t3 b on a.id=b.id
/*
id name rule
----------- ----- -----------------------------------
1 ddd Q1:A、B;Q2:C、D;Q3:B、C;Q4:A、C
(1 row(s) affected)
*/
[解决办法]
--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([ID] VARCHAR(2),[value] INT)
INSERT #tb
SELECT 'Q1',10001 UNION ALL
SELECT 'Q2',20001 UNION ALL
SELECT 'Q3',30001 UNION ALL
SELECT 'Q4',40001
--> 测试数据：#ta
IF OBJECT_ID('TEMPDB.DBO.#ta') IS NOT NULL DROP TABLE #ta
GO
CREATE TABLE #ta([id] VARCHAR(20),[name] VARCHAR(8),[rule] XML)
INSERT #ta
SELECT '1','ddd','<legendRule>
<questionRule>
<questionCondition id="Q1">
<answer>A</answer>
<answer>B</answer>
</questionCondition >
<questionCondition id="Q2">
<answer>C</answer>
<answer>D</answer>
</questionCondition>
<questionCondition id="Q3">
<answer>B</answer>
<answer>C</answer>
</questionCondition>
<questionCondition id="Q4">
<answer>A</answer>
<answer>C</answer>
</questionCondition>
</questionRule>
</legendRule>
'
--------------开始查询--------------------------
;WITH t AS
(
SELECT a.[id],a.[name],T.c.value('(@id)[1]','varchar(10)') AS qid
, T.c.value('(./answer)[1]','varchar(10)') AS answer1
, T.c.value('(./answer)[2]','varchar(10)') AS answer2
FROM #ta AS a
CROSS APPLY a.[rule].nodes('/legendRule/questionRule/questionCondition') T(c)
)
, t2 AS(
SELECT t.[id],t.[name],
(SELECT LTRIM(b.[value])+':'+t.[answer1]+'、'+t.[answer2]+';' FROM #tb b WHERE t.qid=b.id FOR XML PATH('')) AS col
FROM t
)
SELECT DISTINCT [id],[name],col=(SELECT col+'' FROM t2 WHERE [id]=b.[id] AND [name]=b.[name] FOR XML PATH('')) FROM t2 AS b
----------------结果----------------------------
/*
idnamecol
1ddd10001:A、B;20001:C、D;30001:B、C;40001:A、C;
*/
[解决办法]
create table 表A
(id int, name varchar(5), [rule] xml)
insert into 表A(id,name,[rule])
select 1, 'ddd',
'<legendRule>
<questionRule>
<questionCondition id="Q1">
<answer>A</answer>
<answer>B</answer>
</questionCondition >
<questionCondition id="Q2">
<answer>C</answer>
<answer>D</answer>
</questionCondition>
<questionCondition id="Q3">
<answer>B</answer>
<answer>C</answer>
</questionCondition>
<questionCondition id="Q4">
<answer>A</answer>
<answer>C</answer>
</questionCondition>
</questionRule>
</legendRule>'
create table 表B
(ID varchar(5), value varchar(10))
insert into 表B
select 'Q1', '10001' union all
select 'Q2', '20001' union all
select 'Q3', '30001' union all
select 'Q4', '40001'
with t1 as
(select a.id,
o.value('../@id','varchar(5)') 'ids',
o.value('.','varchar(5)') 'ans'
from 表A a
cross apply [rule].nodes('/legendRule/questionRule/questionCondition/answer') x(o)
),t2 as
(select a.id,
a.value+':'+stuff((select '、'+b.ans from t1 b
where b.id=a.id and a.ids=b.ids
for xml path('')),1,1,'') 'ids2'
from
(select d.id,d.ids,c.value,d.ans
from t1 d
inner join 表B c on d.ids=c.ID
) a
group by a.id,a.ids,a.value
),t3 as
(select a.id,
stuff((select ';'+b.ids2 from t2 b where b.id=a.id for xml path('')),1,1,'') 'rule'
from t2 a
group by a.id
)
select a.id,
a.name,
b.[rule]
from 表A a
left join t3 b on a.id=b.id
/*
id name rule
----------- ----- ------------------------------------------------
1 ddd 10001:A、B;20001:C、D;30001:B、C;40001:A、C
(1 row(s) affected)
*/