使用sql查询生成XML [英] GenerateXML using sql query
本文介绍了使用sql查询生成XML的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用sql查询生成XML文件。
I想要我的输出:
< chart caption = 示例标题 showLegend = 1 yAxisName = 合规性和不合规性的百分比 showNames = 1 decimalPrecision = 0 formatNumberScale = 0 >
< categories>
< category label = 2001 />
< category label = 2002 />
< category label = 2003 />
< category label = 2004 />
< category label = 2005 />
< / 类别 >
< dataset seriesName = 符合 >
< set value = 25601 />
< set value = 25601 />
< set value = 25601 />
< set value = 25601 />
< set value = 25601 />
< / 数据集 >
< dataset seriesName = Non_compliant >
< set value = 20148 />
< set value = 20148 />
< set value = 20148 />
< set value = 20148 />
< set value = 20148 />
< / 数据集 >
< / chart >
以下是我用过的查询
选择
(
选择
cast(DAY) (report_date) as varchar( 30 ))+ ' - ' + cast(month(report_date) as varchar( 10 ))+ ' - + cast(YEAR(report_date)< span class =code-keyword> as varchar( 10 )) as '跨度> <跨度class =code-string> category / @ label'
from mssp_eps_compliance_report
for xml路径(' categories'),输入),
(选择 COMPLIANT as ' set / @ value'
from mssp_eps_compliance_report
for xml路径(' dataset'),输入),
(选择 non_compliant as ' set / @ value'
来自 mssp_eps_compliance_report
for xml path(' < span class =code-string> dataset'),type)
for xml path(' '),
Root(' chart')
我的输出是这样的。
< chart>
< categories>
< category label = 9-12-2013 />
< / 类别 >
< categories>
< category label = 9-12-2013 />
< / 类别 >
< categories>
< category label = 9-12-2013 />
< / 类别 >
< categories>
< category label = 9-12-2013 />
< / 类别 >
< dataset>
< set value = 82 />
< / 数据集 >
< dataset>
< set value = 15653 />
< / 数据集 >
< dataset>
< set value = 15446 />
< / 数据集 >
< dataset>
< set value = 13486 />
< / 数据集 >
解决方案
以下代码检索列表 客户优先和姓氏和存储数据 in XML:
SELECT ( select first_name, last_name)
FOR
XML PATH('' customers'),
ROOT(' CustomerList'跨度>)
Hi,
I am trying to generate a XML file using sql query.
I want my output like this:
<chart caption="Sample caption" showLegend="1" yAxisName="% of Compliance and non compliance" showNames="1" decimalPrecision="0" formatNumberScale="0">
<categories>
<category label="2001" />
<category label="2002" />
<category label="2003" />
<category label="2004" />
<category label="2005" />
</categories>
<dataset seriesName="Compliant">
<set value="25601" />
<set value="25601" />
<set value="25601" />
<set value="25601" />
<set value="25601" />
</dataset>
<dataset seriesName=" Non_compliant">
<set value="20148" />
<set value="20148" />
<set value="20148" />
<set value="20148" />
<set value="20148" />
</dataset>
</chart>
Below is the query I have used
select
(
select
cast(DAY(report_date) as varchar(30)) + '-' + cast(month(report_date) as varchar(10)) + '-' + cast(YEAR(report_date) as varchar(10)) as 'category/@label'
from mssp_eps_compliance_report
for xml path('categories'),type),
(select COMPLIANT as 'set/@value'
from mssp_eps_compliance_report
for xml path('dataset'),type),
(select non_compliant as 'set/@value'
from mssp_eps_compliance_report
for xml path('dataset'),type)
for xml path(''),
Root('chart')
I am getting my output like this.
<chart>
<categories>
<category label="9-12-2013" />
</categories>
<categories>
<category label="9-12-2013" />
</categories>
<categories>
<category label="9-12-2013" />
</categories>
<categories>
<category label="9-12-2013" />
</categories>
<dataset>
<set value="82" />
</dataset>
<dataset>
<set value="15653" />
</dataset>
<dataset>
<set value="15446" />
</dataset>
<dataset>
<set value="13486" />
</dataset>
解决方案
The following code retrieves a list of customer first and last names and stores the data in XML: SELECT (select first_name, last_name) FOR XML PATH('customers'), ROOT('CustomerList')
这篇关于使用sql查询生成XML的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文