将每个子标记转换为具有多个分隔符的单列-SQL Server (2) [英] Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (2)
问题描述
declare @xml xml='<plan>
<prescriptions id="1">
<prescription>
<name>ABC</name>
<frequency>Daily</frequency>
<dailyfrequency>
<morning>2</morning>
<afternoon></afternoon>
<night>1</night>
</dailyfrequency>
<dayfrequency></dayfrequency>
</prescription>
<prescription>
<name>EDF</name>
<frequency>Daily</frequency>
<dailyfrequency>
<morning>5</morning>
<afternoon>5</afternoon>
<night>1</night>
</dailyfrequency>
<dayfrequency></dayfrequency>
</prescription>
<prescription>
<name>YTER</name>
<frequency>Weekly</frequency>
<dailyfrequency>
<morning>5</morning>
<afternoon>5</afternoon>
<night>1</night>
</dailyfrequency>
<dayfrequency>Monday,Tuesday,Wednesday</dayfrequency>
</prescription>
</prescriptions>
<prescriptions id="2">
<prescription>
<name>YTRE</name>
<frequency>Daily</frequency>
<dailyfrequency>
<morning>2</morning>
<afternoon></afternoon>
<night>1</night>
</dailyfrequency>
<dayfrequency></dayfrequency>
</prescription>
</prescriptions>
</plan>'
我们可以像下面这样查询,用多个分隔符分隔每个元素,以导致同一列.
We can query like below to segregate each elements with multiple delimiter to be resulted in same column.
SELECT STUFF(
(
SELECT '!' + STUFF(p.query(N'for $n in .//*[local-name()!="dailyfrequency"]
return <a>{concat("$",($n/text())[1])}</a>'
).value(N'.',N'nvarchar(max)'),1,1,'')
FROM @xml.nodes(N'/plan/prescriptions/prescription') AS A(p)
FOR XML PATH(''),TYPE).value(N'.',N'nvarchar(max)'),1,1,'')
结果:
ABC$Daily$2$$1$!EDF$Daily$5$5$1$!YTER$Weekly$5$5$1$Monday,Tuesday,Wednesday!YTRE$Daily$2$$1$
但这里的问题是这会将所有标签组合在一个列下.当我们查看xml时,它有2个主要部门Prescription id=1和prescritpion id=2.所以我们的最终结果将是这样的
But the problem here is this will combine all the tags under a single column.When we look the xml it has 2 major division Prescription id=1 and prescritpion id=2.So our final result will be like this
结果:
ABC$Daily$2$$1$!EDF$Daily$5$5$1$!YTER$Weekly$5$5$1$Monday,Tuesday,Wednesday
YTRE$Daily$2$$1$
我认为我们必须为每个 <Prescriptions> 粉碎 Xml 然后必须计算这个
I think we have to Shred the Xml for each <Prescriptions> then have to calculate this
有人可以在这里解决这个问题
Could someone please here to solve this
提前致谢,Jayendran
Thanks in advance,Jayendran
推荐答案
像这样改变你的查询:
SELECT STUFF(
(
SELECT '!' + STUFF(p.query(N'for $n in .//*[local-name()!="dailyfrequency"]
return <a>{concat("$",($n/text())[1])}</a>'
).value(N'.',N'nvarchar(max)'),1,1,'')
FROM p.nodes(N'prescription') AS A(p)
FOR XML PATH(''),TYPE).value(N'.',N'nvarchar(max)'),1,1,'')
FROM @xml.nodes(N'/plan/prescriptions') AS A(p);
首先我们为不同的处方创建一个派生表,其次我们对每个处方分别使用以前的代码.
First we create a derived table for the different prescriptions, Secondly we use the former code for each prescription separately.
这篇关于将每个子标记转换为具有多个分隔符的单列-SQL Server (2)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!