I have requirement like below.
我有如下要求。
And ddl and dml script for above image is
上面的图像的ddl和dml脚本是
CREATE TABLE #example
([CCP_DETAILS_SID] int, [ACCOUNT_GROWTH] int, [PRODUCT_GROWTH] int, [PROJECTION_SALES] numeric(22,6), [PROJECTION_UNITS] numeric(22,6), [PERIOD_SID] int)
;
INSERT INTO #example
([CCP_DETAILS_SID], [ACCOUNT_GROWTH], [PRODUCT_GROWTH], [PROJECTION_SALES], [PROJECTION_UNITS], [PERIOD_SID])
VALUES
(30001, 0, 0, 1505384.695, 18487.25251, 1801),
(30001, 0, 0, 1552809.983, 18695.75536, 1802),
(30001, 0, 0, 1595642.121, 18834.75725, 1803),
(30002, 0, 0, 10000.32, 18834.75725, 1801),
(30002, 0, 0, 1659124.98, 18834.75725, 1802),
(30002, 0, 0, 465859546.6, 18834.75725, 1803)
;
And i have to convert above results to xml format like below (Output).
我必须将上面的结果转换成如下所示的xml格式(输出)。
ccp_details_sid xml_format_string
30001 <period>
<period_sid period_sid=1801>
<PROJECTION_SALES>1505384.695</PROJECTION_SALES>
<PROJECTION_UNITS>18487.25251<PROJECTION_UNITS>
<ACCOUNT_GROWTH>0</ACCOUNT_GROWTH>
<PRODUCT_GROWTH>0</PRODUCT_GROWTH>
</period_sid>
<period_sid period_sid=1802>
<PROJECTION_SALES>1552809.983</PROJECTION_SALES>
<PROJECTION_UNITS>18695.75536<PROJECTION_UNITS>
<ACCOUNT_GROWTH>0</ACCOUNT_GROWTH>
<PRODUCT_GROWTH>0</PRODUCT_GROWTH>
</period_sid>
<period_sid period_sid=1802>
<PROJECTION_SALES>1595642.121</PROJECTION_SALES>
<PROJECTION_UNITS>18834.75725<PROJECTION_UNITS>
<ACCOUNT_GROWTH>0</ACCOUNT_GROWTH>
<PRODUCT_GROWTH>0</PRODUCT_GROWTH>
</period_sid>
</period>
30002 Same like above
I am new to XML
so couldn't able to do it quickly. I have used Marc_s solution with cross apply
but can't able to achieve it.
我刚接触XML,所以不能很快地做。我使用了交叉应用的Marc_s解决方案,但无法实现。
Note: my major goal is, in above image if we see there are three records for single ccp_details_sid so I want to convert it as one row by using XML
(mentioned above).
注意:我的主要目标是,在上面的图像中,如果我们看到单个ccp_details_sid有三条记录,那么我希望使用XML将其转换为一行(上面提到过)。
2 个解决方案
#1
13
The following will work for you:
以下是对你有用的:
SELECT t.CCP_DETAILS_SID,
( SELECT PERIOD_SID AS [@period_sid],
x.PROJECTION_SALES,
x.PROJECTION_UNITS,
x.ACCOUNT_GROWTH,
x.PRODUCT_GROWTH
FROM #Example AS x
WHERE x.CCP_DETAILS_SID = t.CCP_DETAILS_SID
FOR XML PATH('period_sid'), TYPE, ROOT('period')
) AS xml_format_string
FROM #Example AS t
GROUP BY t.CCP_DETAILS_SID;
It essentially gets all your unique values for CCP_DETAILS_SID
using:
它使用以下命令获取CCP_DETAILS_SID的所有惟一值:
SELECT t.CCP_DETAILS_SID
FROM #Example AS t
GROUP BY t.CCP_DETAILS_SID;
Then for each of these values uses the correlated subquery to form the XML. With the key points being:
然后,对于每个值,使用相关的子查询来形成XML。重点是:
- Use
@
in front of the alias to create a property, e.g.AS [@period_sid]
- 在别名前面使用@来创建属性,例如[@period_sid]
- Use
PATH('period_sid')
to name the container for each row - 使用路径('period_sid')为每一行命名容器
- Use
ROOT('period')
to name the outer nodes. - 使用ROOT('period')来命名外部节点。
例子DBFiddle
#2
4
I would use following approach:
我将采用以下方法:
SELECT *
FROM (SELECT DISTINCT x.[CCP_DETAILS_SID] FROM #example x) y
OUTER APPLY (
SELECT (
SELECT z.[PERIOD_SID] AS '@period_sid',
z.[PROJECTION_SALES] AS 'PROJECTION_SALES',
z.[PROJECTION_UNITS] AS 'PROJECTION_UNITS',
z.[ACCOUNT_GROWTH] AS 'ACCOUNT_GROWTH',
z.[PRODUCT_GROWTH] AS 'PRODUCT_GROWTH'
FROM #example z
WHERE y.[CCP_DETAILS_SID] = z.[CCP_DETAILS_SID]
FOR XML PATH('period_sid'), ROOT('period'), TYPE
) AS XmlResults
) z
演示
#1
13
The following will work for you:
以下是对你有用的:
SELECT t.CCP_DETAILS_SID,
( SELECT PERIOD_SID AS [@period_sid],
x.PROJECTION_SALES,
x.PROJECTION_UNITS,
x.ACCOUNT_GROWTH,
x.PRODUCT_GROWTH
FROM #Example AS x
WHERE x.CCP_DETAILS_SID = t.CCP_DETAILS_SID
FOR XML PATH('period_sid'), TYPE, ROOT('period')
) AS xml_format_string
FROM #Example AS t
GROUP BY t.CCP_DETAILS_SID;
It essentially gets all your unique values for CCP_DETAILS_SID
using:
它使用以下命令获取CCP_DETAILS_SID的所有惟一值:
SELECT t.CCP_DETAILS_SID
FROM #Example AS t
GROUP BY t.CCP_DETAILS_SID;
Then for each of these values uses the correlated subquery to form the XML. With the key points being:
然后,对于每个值,使用相关的子查询来形成XML。重点是:
- Use
@
in front of the alias to create a property, e.g.AS [@period_sid]
- 在别名前面使用@来创建属性,例如[@period_sid]
- Use
PATH('period_sid')
to name the container for each row - 使用路径('period_sid')为每一行命名容器
- Use
ROOT('period')
to name the outer nodes. - 使用ROOT('period')来命名外部节点。
例子DBFiddle
#2
4
I would use following approach:
我将采用以下方法:
SELECT *
FROM (SELECT DISTINCT x.[CCP_DETAILS_SID] FROM #example x) y
OUTER APPLY (
SELECT (
SELECT z.[PERIOD_SID] AS '@period_sid',
z.[PROJECTION_SALES] AS 'PROJECTION_SALES',
z.[PROJECTION_UNITS] AS 'PROJECTION_UNITS',
z.[ACCOUNT_GROWTH] AS 'ACCOUNT_GROWTH',
z.[PRODUCT_GROWTH] AS 'PRODUCT_GROWTH'
FROM #example z
WHERE y.[CCP_DETAILS_SID] = z.[CCP_DETAILS_SID]
FOR XML PATH('period_sid'), ROOT('period'), TYPE
) AS XmlResults
) z
演示