問(wèn)題描述

我的 xml:

declare @x xml='<ROll ID="1"><考試><考試詳情日期="2017-04-02 13:30:00"><考試名稱="ECO" Total="100">150</Exam><考試名稱="BIO" Total="150">50</Exam><考試名稱=數(shù)學(xué)"總計(jì)=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></詳細(xì)信息>'

當(dāng)我嘗試使用從

但我想將其查詢回

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

請(qǐng)幫我解決這個(gè)復(fù)雜的問(wèn)題

提前致謝，Jayendran

在這種情況下，我會(huì)離開(kāi)通用路徑并像這樣構(gòu)建它:

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);

為什么通用解決方案可能不起作用的主要問(wèn)題:

• 一般很難混合屬性和元素的text()
• (重要！)不保證屬性順序！屬性值可能未按預(yù)期順序出現(xiàn)...

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...

這篇關(guān)于將每個(gè)子標(biāo)記轉(zhuǎn)換為具有多個(gè)分隔符的單列-SQL Server (3)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！