使用T-SQL将列转换为XML列，每行一个XML。

Question

我所拥有的示例：

表格：(col1 int, col2 int)
样本数据 (真实数据在运行时不会被知晓)：

1,1
2,2
3,3
4,4

预期结果：仅有一列（xml）
和4行

row1: <cols><col1>1</col1><col2>1</col2></cols>
row2: <cols><col1>2</col1><col2>2</col2></cols>
row3: <cols><col1>3</col1><col2>3</col2></cols>
row4: <cols><col1>4</col1><col2>4</col2></cols>

抱歉，管理员不希望删除“噪音答案”。我希望他能理解我在帮助这些可怜的人，让别人看不出他们完全错了。我在保护他们的声誉...

以下是实现我的方法。但这不是一个好方法，因为XML是手动构建的，而且没有正确编码。

declare @colsList nvarchar(max)
set @colsList = ''
select @colsList = @colsList + '+ ''<' + COLUMN_NAME + '>'' + Isnull(cast( [' + COLUMN_NAME + '] as nvarchar(max)),'''') + ''</' + COLUMN_NAME + '>'' ' 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = 'SampleTable';

select @colsList = stuff(@colsList,1,1,'');
exec('select colsValues=cast(' + @colsList + ' as xml) from SampleTable');

- Softlion

---

Softion，请停止试图删除人们的答案，因为它们“完全错了”。相反，要在您的问题或对其答案的评论中澄清您实际上正在询问什么。 - Stephen Canon

我在删除之前已经澄清了问题。我删除是为了不让其他用户受到与此特定问题无关的答案的干扰。 - Softlion

你以为你在哪里？在学校吗？这是我的问题，我可以自己处理。 - Softlion

Answer 1

简单就是快。

select d=(select a.* for xml path('r'),type,elements absent)
from MyTable a

- Softlion

Answer 2

最简单的方法是使用以下内容：

SELECT (SELECT T.* FOR XML RAW)
FROM MyTable T

如果您想更好地控制XML结构，您应该使用：

SELECT (SELECT T.* FROM (SELECT T.*) T FOR XML AUTO)
FROM MyTable T

- Char

Answer 3

编辑

要为每一行生成一个XML列，您可以使用以下方法：

-- this is a poor cousin of a proper sanitize function. Add to it whatever you need
-- to cater for your column names that will be invalid XML tag names
-- example below only removes spaces (xml node names cannot contain spaces)
create function dbo.sanitize(@colname sysname) returns sysname
as begin
return replace(@colname,' ', '')
end
GO

-- test table
create table [test table](i int, s varchar(max), [d t] datetime)
insert [test table] select 1, 'abc', getdate()
insert [test table] select 2, 'def', getutcdate()
insert [test table] select 3, 'g', getdate()+10
insert [test table] select 4, 'hij', getutcdate()+20
GO

-- the dynamic SQL to return each row as an single XML column
declare @tablename sysname set @tablename = 'test table';
declare @colsList nvarchar(max)
select @colsList = isnull(@colsList+',','') +
    QuoteName(column_name) + ' as ' +
    dbo.sanitize(column_name)
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = @tablename;

select @colsList = '
    select (select ' + @colslist + ' for xml path (''cols''))
    from ' + quotename(@tablename)
--print @colslist
exec(@colslist);

作为整个表的单个 XML 文档

只需使用 FOR XML，并指定路径为 "cols"

declare @tbl table (col1 int, col2 int)
insert @tbl values (1,1)
insert @tbl values (2,2)
insert @tbl values (3,3)
insert @tbl values (4,4)

-- you can use just the part below here
select col1, col2
from @tbl
for xml path('cols')

输出

<cols>
  <col1>1</col1>
  <col2>1</col2>
</cols>
<cols>
  <col1>2</col1>
  <col2>2</col2>
</cols>
<cols>
  <col1>3</col1>
  <col2>3</col2>
</cols>
<cols>
  <col1>4</col1>
  <col2>4</col2>
</cols>

请注意，对于 XML，格式和空格没有固有的含义。因此，您要么想要一个类似 XML 的带有多行的 VARCHAR 结果，要么想要 XML - 选择其中一个。

- RichardTheKiwi

---

我认为OP想要每行一个XML文档。 - Martin Smith

预期结果：仅一列（xml）和4行。 - Softlion

@Softion 欢迎来到StackOverflow。编辑不是为了删除你认为没有回答问题的答案，实际上它并不能删除答案，只会使其比之前更加不相关。 - Pascal Cuoq

很好。如果它可以从我的视野中隐藏，那就比仅仅使它不那么重要更好。如果它们能够增加一些价值，我就不会删除这些答案。但是它们没有。它们只属于它们的作者。 - Softlion

不错的尝试。在MyTable中选择结果=（选择myColumn for xml path）是一个奇怪的结构，因为内部选择没有from子句。它不能与选择列表中的*一起使用。它真的被SQL Server支持还是一个黑客？好吧，它确实有效。谢谢！ - Softlion

@softion，我看到你推断它可以与*一起使用。太棒了！ - RichardTheKiwi