sql:xml读取解决思路

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)
*/

查看更多 下一篇