本文介绍了将每个子标记转换为具有多个分隔符的单列-SQL Server (3)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的 xml:
declare @x xml='<ROll ID="1"><考试><考试详情日期="2017-04-02 13:30:00"><考试名称="ECO" Total="100">150</Exam><考试名称="BIO" Total="150">50</Exam><考试名称=数学"总计=200">28</考试></考试详情><考试详情日期="2017-04-02 14:30:00"><考试名称="ENGLISH" Total="100">150</Exam><考试名称="BIO" Total="200">50</Exam><考试名称="ZIO" Total="250">28</Exam></考试详情></考试></ROll><ROll ID="2"><考试><考试详情日期="2017-05-02 13:30:00"><考试名称="HIS" Total="100">150</Exam><考试名称="BIO" Total="200">50</Exam><考试名称="THI" Total="200">89</Exam></考试详情></考试></ROll></详细信息>' 当我尝试使用从
但我想将其查询回
2017-04-02 13:30:00$ECO$100$150！2017-04-02 13:30:00$BIO$150$50！2017-04-02 13:30:00$MATH$200$28!2017-04-02 14:30:00$ENGLISH$100$150！2017-04-02 14:30:00$BIO$200$50！2017-04-02 14:30:00$ZIO$250$282017-05-02 13:30:00$HIS$100$150！2017-05-02 13:30:00$BIO$200$50！2017-05-02 13:30:00$THI$200$89请帮我解决这个复杂的问题
提前致谢，Jayendran
解决方案
在这种情况下，我会离开通用路径并像这样构建它:
SELECT r.value(N'@ID',N'int') AS ROll_ID，东西((选择(SELECT '!'+ed.value(N'@date',N'nvarchar(max)')+'$' + e.value(N'@name','nvarchar(max)')+'$' + e.value(N'@Total','nvarchar(max)')+'$' + e.value(N'text()[1]','nvarchar(max)')FROM ed.nodes(N'Exam') AS D(e)FOR XML PATH(''),TYPE).value(N'text()[1]','nvarchar(max)')FROM ex.nodes(N'Examdetails') AS C(ed)FOR XML PATH(''),TYPE).value(N'text()[1]','nvarchar(max)'),1,1,'')FROM @x.nodes(N'/Detail/ROll') AS A(r)CROSS APPLY r.nodes(N'Exams') AS B(ex);为什么通用解决方案可能不起作用的主要问题:
- 一般很难混合属性和元素的
text() - (重要！)不保证属性顺序！属性值可能未按预期顺序出现...
My xml:
declare @x xml='<Detail>
<ROll ID="1">
<Exams>
<Examdetails date="2017-04-02 13:30:00">
<Exam name="ECO" Total="100">150</Exam>
<Exam name="BIO" Total="150">50</Exam>
<Exam name="MATH" Total="200">28</Exam>
</Examdetails>
<Examdetails date="2017-04-02 14:30:00">
<Exam name="ENGLISH" Total="100">150</Exam>
<Exam name="BIO" Total="200">50</Exam>
<Exam name="ZIO" Total="250">28</Exam>
</Examdetails>
</Exams>
</ROll>
<ROll ID="2">
<Exams>
<Examdetails date="2017-05-02 13:30:00">
<Exam name="HIS" Total="100">150</Exam>
<Exam name="BIO" Total="200">50</Exam>
<Exam name="THI" Total="200">89</Exam>
</Examdetails>
</Exams>
</ROll>
</Detail>'
I want to Segregate my xml based on ROLL ID's while i tried with the below query referred from here
SELECT STUFF(
(
SELECT '!' + STUFF(p.query(N'for $n in .//*
return <a>{concat("$",($n/text())[1])}</a>'
).value(N'.',N'nvarchar(max)'),1,1,'')
FROM p.nodes(N'Exams') AS A(p)
FOR XML PATH(''),TYPE).value(N'.',N'nvarchar(max)'),1,1,'')
FROM @x.nodes(N'Detail/ROll') AS A(p);
I get the result as
But i want to query it back as
2017-04-02 13:30:00$ECO$100$150!2017-04-02 13:30:00$BIO$150$50!2017-04-02 13:30:00$MATH$200$28!2017-04-02 14:30:00$ENGLISH$100$150!2017-04-02 14:30:00$BIO$200$50!2017-04-02 14:30:00$ZIO$250$28
2017-05-02 13:30:00$HIS$100$150!2017-05-02 13:30:00$BIO$200$50!2017-05-02 13:30:00$THI$200$89
Kindly help me solve this complexity
Thanks in advance ,Jayendran
解决方案
In this case I'd leave the generical path and build it up like this:
SELECT r.value(N'@ID',N'int') AS ROll_ID
,STUFF((
SELECT
(
SELECT '!'+ed.value(N'@date',N'nvarchar(max)')
+'$' + e.value(N'@name','nvarchar(max)')
+'$' + e.value(N'@Total','nvarchar(max)')
+'$' + e.value(N'text()[1]','nvarchar(max)')
FROM ed.nodes(N'Exam') AS D(e)
FOR XML PATH(''),TYPE
).value(N'text()[1]','nvarchar(max)')
FROM ex.nodes(N'Examdetails') AS C(ed)
FOR XML PATH(''),TYPE
).value(N'text()[1]','nvarchar(max)'),1,1,'')
FROM @x.nodes(N'/Detail/ROll') AS A(r)
CROSS APPLY r.nodes(N'Exams') AS B(ex);
Main issues why a generic solution might not work:
- it is difficult to mix attributes and element's
text()generically - (important!) the attributes order is not guaranteed! Attribute values might appear not in order expected...
这篇关于将每个子标记转换为具有多个分隔符的单列-SQL Server (3)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！