选择结果集的几列作为 XML [英] Selecting few columns of a result set as XML
问题描述
我有一个简单的 SELECT
语句,它从单个表中选择几列:
I have a simple SELECT
statement which selects few columns from a single table:
SELECT id, name, phone, address
FROM tmp_user
是否可以更改此查询,以便只有 id
和 name
位于选择中,而其余详细信息位于 xml 节点中?
Is it possible to change this query so that only id
and name
are in select and remaining details are in a xml node?
我希望这个选择的输出应该是
I expected output of this select should be
id name extra data
1 Shreedhar <data><phone>...</phone><address>...</address></data>
2 John Doe <data><phone>...</phone><address>...</address></data>
3 Jane Doe <data><phone>...</phone><address>...</address></data>
返回表的最后一列应该是带有所需数据的 XML 类型.我知道如何使用 FOR XML 将整个结果集转换为 XML.但是,我只查找要转换的部分列.可能吗?
The last column is of the returned table should be of XML type with required data. I know how the entire result set can be converted to XML using FOR XML. However I am looking only for part of the columns to be converted. Is it possible?
推荐答案
好的!没问题 - 试试这样的:
Sure! No problem - try something like this:
SELECT
id, name,
(SELECT phone, address
FROM dbo.tmp_user u2
WHERE u2.id = u1.id
FOR XML PATH('data')) AS 'ExtraData'
FROM
dbo.tmp_user u1
这给了我一个非常像你正在寻找的输出.
This gives me an output pretty exactly like the one you're looking for.
这篇关于选择结果集的几列作为 XML的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!