问题描述
我的 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...