对于带有临时表的XML Union

I know how to do union's and spit out an XML file from different tables, however, I need to create a temp table that will house 3 records that I need to be a part of the XML File. The structure is exactly the same as the other tables.

我知道如何执行union，并从不同的表中生成XML文件，但是，我需要创建一个临时表，其中包含3条记录，我需要成为XML文件的一部分。结构与其他表完全相同。

How would I go about doing this?

我该怎么做呢?

select * from
(
  select ID_Number as [ID], CLAST as [name/last], CFIRST as [name/first], '' as extension]
  from dbo.users as a 
  union all
  select PID as [ID], NID as [name/last], NAME as [name/first],  PREF_TITLE as [extension] 
  from dbo.Person
) as a
FOR XML PATH('employee'), ROOT('employees')

So I would need 3 lines of data, which will fill ID, name/last, name/first and extension.

所以我需要3行数据，它们将填充ID、name/last、name/first和extension。

What would be the best recommendation?

最好的建议是什么?

1 个解决方案

#1

Create the temp table, fill it and add it into the query as another UNION ALL. There are different types of temporary table, here's some code that uses a table variable, the most simple type...

创建临时表,填满它,并将它添加到查询作为另一个联盟。有不同类型的临时表,这是一些代码,使用一个表变量,最简单的类型……

DECLARE @tempTable TABLE
(
  ID int,
  Surname varchar(30),
  FirstName varchar(30),
  Title varchar(30)
)

INSERT @tempTable VALUES (7, 'Siobhan', 'Green', 'Ms')
INSERT @tempTable VALUES (8, 'Paul', 'Jones', 'Mr')
INSERT @tempTable VALUES (9, 'Sam', 'Morrison', 'Mrs')

SELECT * FROM
(
  SELECT ID_Number as [ID], CLAST as [name/last], CFIRST as [name/first], '' as [extension]
  FROM users as a 
  UNION ALL
  SELECT PID, NID, NAME, PREF_TITLE 
  FROM Person
  UNION ALL
  SELECT ID, Surname, FirstName, Title
  FROM @tempTable
) as a
FOR XML PATH('employee'), ROOT('employees')

Click here to see it in action at SQL Fiddle

点击这里查看SQL Fiddle

#1