本文介紹了將每個子標記轉換為具有多個分隔符的單列-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)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！