如何查询SQL Server并生成列名称和值作为属性的XML
问题描述:
我想弄清楚如何查询生成如下所示的xml的表格: (这是AdventureWorks数据库的示例。如何查询SQL Server并生成列名称和值作为属性的XML
我可以很容易地得到列名作为元素,但是有可能使列名称和值作为属性?我试图找出如何以通用的方式做到这一点,所以我不知道不想使用FOR EXPLICIT对列名进行硬编码
<TABLE name="StateProvince">
<ROW>
<COL name="StateProvinceID" value="1" />
<COL name="StateProvinceCode" value="AB" />
<COL name="CountryRegionCode" value="CA" />
<COL name="IsOnlyStateProvinceFlag" value="0" />
<COL name="Name" value="Alberta" />
<COL name="TerritoryID" value="6" />
<COL name="rowguid" value="298C2880-AB1C-4982-A5AD-A36EB4BA0D34" />
<COL name="ModifiedDate" value="2004-03-11T10:17:21.587" />
</ROW>
</TABLE>
答
在不指定列名的情况下做到这一点有点棘手,但可行。
要使用此功能,您需要用您的表名替换@T
,并将表名称常量'StateProvince'
更改为您的表名称。
declare @T table
(
StateProvinceID int,
StateProvinceCode char(2),
CountryRegionCode char(2),
IsOnlyStateProvinceFlag int,
Name varchar(50),
TerritoryID int,
rowguid uniqueidentifier,
ModifiedDate datetime
)
insert into @T values
(1, 'AB', 'CA', 0, 'Alberta', 6, '298C2880-AB1C-4982-A5AD-A36EB4BA0D34', '2004-03-11T10:17:21.587'),
(2, 'AB', 'CA', 0, 'Alberta', 6, '298C2880-AB1C-4982-A5AD-A36EB4BA0D34', '2004-03-11T10:17:21.587')
select 'StateProvince' as [@name],
(
select
(
select T3.N.value('local-name(.)', 'sysname') as [@name],
T3.N.value('.', 'nvarchar(max)') as [@value]
from (
select T1.*
for xml path(''), type
) T2(N)
cross apply T2.N.nodes('*') as T3(N)
for xml path('COL'), root('ROW'), type
)
from @T as T1
for xml path(''), type
)
for xml path('TABLE')
结果:
<TABLE name="StateProvince">
<ROW>
<COL name="StateProvinceID" value="1" />
<COL name="StateProvinceCode" value="AB" />
<COL name="CountryRegionCode" value="CA" />
<COL name="IsOnlyStateProvinceFlag" value="0" />
<COL name="Name" value="Alberta" />
<COL name="TerritoryID" value="6" />
<COL name="rowguid" value="298C2880-AB1C-4982-A5AD-A36EB4BA0D34" />
<COL name="ModifiedDate" value="2004-03-11T10:17:21.587" />
</ROW>
<ROW>
<COL name="StateProvinceID" value="2" />
<COL name="StateProvinceCode" value="AB" />
<COL name="CountryRegionCode" value="CA" />
<COL name="IsOnlyStateProvinceFlag" value="0" />
<COL name="Name" value="Alberta" />
<COL name="TerritoryID" value="6" />
<COL name="rowguid" value="298C2880-AB1C-4982-A5AD-A36EB4BA0D34" />
<COL name="ModifiedDate" value="2004-03-11T10:17:21.587" />
</ROW>
</TABLE>
注:表格中的列名必须是一个有效的XML元素名称。例如,您可以在列名中没有任何空格。
答
我不认为您可以使用S来实现您正在寻找的100% QL Server的功能 - 您可以关闭,但不是100%。
我查询这里使用可用的FOR XML PATH
构建在SQL Server 2005及更高版本:
SELECT
[StateProvinceID] AS 'COL/@StateProvinceID',
'',
[StateProvinceCode] AS 'COL/@StateProvinceCode',
'',
[CountryRegionCode] AS 'COL/@CountryRegionCode',
'',
[IsOnlyStateProvinceFlag] AS 'COL/@IsOnlyStateProvinceFlag',
'',
[Name] AS 'COL/@Name',
'',
[TerritoryID] AS 'COL/@TerritoryID',
'',
[rowguid] AS 'COL/@rowguid',
'',
[ModifiedDate] AS 'COL/@ModifiedDate'
FROM [Person].[StateProvince]
FOR XML PATH('ROW'), ROOT('TABLE')
导致的XML是这样的:
<TABLE>
<ROW>
<COL StateProvinceID="1" />
<COL StateProvinceCode="AB " />
<COL CountryRegionCode="CA" />
<COL IsOnlyStateProvinceFlag="0" />
<COL Name="Alberta" />
<COL TerritoryID="6" />
<COL rowguid="298C2880-AB1C-4982-A5AD-A36EB4BA0D34" />
<COL ModifiedDate="2008-03-11T10:17:21.587" />
</ROW>
<ROW>
<COL StateProvinceID="2" />
<COL StateProvinceCode="AK " />
<COL CountryRegionCode="US" />
<COL IsOnlyStateProvinceFlag="0" />
<COL Name="Alaska" />
<COL TerritoryID="1" />
<COL rowguid="5B7B8462-A888-4E0B-A3E1-7278F8AF107E" />
<COL ModifiedDate="2008-03-11T10:17:21.587" />
</ROW>
..........
</TABLE>
您需要的 “空” 栏选择在每个属性之间避免将所有列值插入单个<COL .... />
元素,如下所示:
<COL StateProvinceID="2"
StateProvinceCode="AK "
CountryRegionCode="US"
IsOnlyStateProvinceFlag="0"
Name="Alaska"
TerritoryID="1"
rowguid="5B7B8462-A888-4E0B-A3E1-7278F8AF107E"
ModifiedDate="2008-03-11T10:17:21.587" />
感谢Mikael,之前我曾见过类似的东西,并希望我失去了一些更容易。 :-) – SumOfDavid 2012-03-03 22:28:14