'For Xml Path'在 SQL Server 中

Question

我正在尝试用"FOR XML PATH"；。

<?xml version="1.0" encoding="utf-8"?>
<List Name=”test name”>
<Columns>
<c>Column1</c>
<c>Column2</c>
<c>Column3</c>
</Columns>
<Rows>  
<r>
<v>Data1Column1</v>
<v>Data1Column2</v>
<v>Data1Column3</v>
</r>
<r>
<v>Data2Column1</v>
<v>Data2Column2</v>
<v>Data2Column3</v>
</r>
</Rows>
</List>

我正试图用以下内容来处理行部分：

SELECT 
[FA No.] as [v],
[Location] as [v]
FROM 
sc.test_rd 
FOR XML PATH (''), ROOT ('Row')

返回的输出为：

<Row>
<v>6930151128</v>
<v>6931151128</v>
<v>6932151128</v>
<v>6945151990</v>
<v>6989151838</v>
</Row>

但我需要以下内容：

<Row>
<v>6930</v><v>151128</v>
<v>6931</v><v>151128</v>
<v>6932</v><v>151128</v>
<v>6945</v><v>151990</v>
<v>6989</v><v>151838</v>
</Row>

如有任何帮助，我们将不胜感激。

Nulls有效，但现在我必须结合这个

SELECT COLUMN_NAME as [c/*] 
FROM INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='test_rd'
FOR XML PATH ('') , ROOT ('Columns');

这给了我一个输出：

<Columns>
<c>id</c>
<c>FA No.</c>
<c>Location</c>
<c>Location Name</c>
<c>Line of Business</c>
<c>LOB Name</c>
<c>Area/CMU/Paypoint</c>
<c>Model Code</c>
</Columns>

用这个

SELECT 
[FA No.] as [v],
null,
[Location] as [v],
null,
[Line of Business] as [v],
null,
[Area/CMU/Paypoint] as [v],
null,
[Model Code] as [v]
FROM sc.test_rd
FOR XML path ('r') , Root ('Row')

这给了我一个输出：

<Row>
<r>
<v>6930</v>
<v>151128</v>
<v>2100</v>
<v>2</v>
<v>CMU3+</v>
</r>
<r>
<v>6931</v>
<v>151128</v>
<v>2100</v>
<v>3</v>
<v>CMU3+</v>
</r>
<r>
<v>6932</v>
<v>151128</v>
<v>2100</v>
<v>1</v>
<v>CMU3+</v>
</r>
<r>
<v>6945</v>
<v>151990</v>
<v>2100</v>
<v>2</v>
<v>CMU3+</v>
</r>
<r>
<v>6989</v>
<v>151838</v>
<v>2100</v>
<v>2</v>
<v>CMU3+</v>
</r>
</Row>

所需输出的另一个示例：

<?xml version="1.0" encoding="utf-8"?>
<List Name='test_rd' Action='Update'>
<Columns><c>FA No.</c><c>Location</c><c>Location Name</c><c>Line of Business</c><c>LOB Name</c><c>Area/CMU/Paypoint</c><c>Model Code</c></Columns>
<Rows>
<r><v>6930</v><v>151128</v><v>Viewmont Mall</v><v>2100</v><v>Strollers</v><v>1</v><v>CMU3+</v></r>
<r><v>6931</v><v>151128</v><v>Viewmont Mall</v><v>2100</v><v>Strollers</v><v>2</v><v>CMU3+</v></r>
<r><v>6932</v><v>151128</v><v>Viewmont Mall</v><v>2100</v><v>Strollers</v><v>3</v><v>CMU3+</v></r>
<r><v>6933</v><v>151128</v><v>Viewmont Mall</v><v>2100</v><v>Strollers</v><v>4</v><v>CMU3+</v></r>
<r><v>6934</v><v>151128</v><v>Viewmont Mall</v><v>2100</v><v>Strollers</v><v>5</v><v>CMU3+</v></r>
</Rows>
</List>

我也想添加一个CASE选项，但我一直收到错误？

-- test_rd
SELECT 'test_rd' as [@Name], 'Update' as [@Action]
, (
SELECT COLUMN_NAME as [c/*] 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='test_rd' AND TABLE_SCHEMA = 'sc' and  COLUMN_NAME != 'id'
FOR XML PATH (''), TYPE, ROOT ('Columns')
)
, (
SELECT 
[FA No.] as [v],
NULL,
[Location] as [v],
NULL,
[Location Name] as [v],
NULL,
[Line of Business]as [v],
NULL,
[Line of Business Name]as [v],
CASE WHEN [Line of Business Name] = '0' THEN ''
WHEN [Line of Business Name] = '1' THEN 'lob1'
WHEN [Line of Business Name] = '2' THEN 'lob2'
End as [Line of Business Name],
NULL,
[Area/CMU/Paypoint] as [v],
NULL,
[Model Code] as [v]
FROM sc.test_rd
FOR XML path ('r'), TYPE, ROOT ('Rows')
)
FOR XML PATH('List') 
-- test_rd

查找错误-SQL Server数据库错误：列名"业务线名称"包含FOR XML所需的无效XML标识符；"(0x0020(是出现故障的第一个字符

Answer 1

看一眼以下内容。。。注意选择中的NULL

示例

Declare @YourTable Table ([FA No.] varchar(50),[Location] varchar(50))
Insert Into @YourTable Values 
(6930,151128)
,(6931,151128)
,(6932,151128)
,(6945,151990)
,(6989,151838)

SELECT 
[FA No.] as [v],
null,
[Location] as [v]
FROM @YourTable
FOR XML path ('') , Root ('Row')

退货

<Row>
<v>6930</v>
<v>151128</v>
<v>6931</v>
<v>151128</v>
<v>6932</v>
<v>151128</v>
<v>6945</v>
<v>151990</v>
<v>6989</v>
<v>151838</v>
</Row>

编辑-

有点不清楚您在寻找什么，但您可以在XML创建中使用TYPE创建子查询。查看Rows =并注意到,TYPE

示例

Select [List/@Name] = YourNameColumn
,Rows = ( 
Select  [FA No.] as [v],
null,
[Location] as [v]
From   sc.test_rd 
FOR XML path ('') , Root ('Row'),TYPE
)
From SomeTable

Answer 2

这里是一个可重复的最小示例。我使用了AdventureWorks2012数据库及其包含三列的Currency窄表。

SQL

USE AdventureWorks2012;
GO
SELECT 'test_rd' AS [@Name], 'Update' AS [@Action]
, (
SELECT COLUMN_NAME as [c/*] 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='Currency' AND TABLE_SCHEMA = 'Sales' AND COLUMN_NAME != 'CurrencyCode'
FOR XML PATH (''), TYPE, ROOT ('Columns')
)
, (
SELECT TOP(2)
[CurrencyCode] as [v],
NULL,
[Name] as [v],
NULL,
[ModifiedDate] as [v]
FROM Sales.Currency
FOR XML path ('r'), TYPE, ROOT ('Row')
)
FOR XML PATH('List'), TYPE;

输出

<List Name="test_rd" Action="Update">
<Columns>
<c>Name</c>
<c>ModifiedDate</c>
</Columns>
<Row>
<r>
<v>AED</v>
<v>Emirati Dirham</v>
<v>2002-06-01T00:00:00</v>
</r>
<r>
<v>AFA</v>
<v>Afghani</v>
<v>2002-06-01T00:00:00</v>
</r>
</Row>
</List>