检索结构化Xml从Sql Server Express 2005

本文关键字：Server Express 2005 Sql 结构化 Xml 检索 | 更新日期: 2023-09-27 17:50:28

我想从全文搜索索引表的xml输出如下所示。但是我的代码在联合附近生成了不正确的语法我的代码

SELECT
Table1.Name 'Table1/Name',
Table1.Email 'Table1/Email',
(    SELECT
     Table2.Address 'Address',
     Table2.Phone 'Phone',
     FROM Details Table2
     INNER JOIN Regd Table3 ON Table3.Code = Table2.Code
     AND (Table3.SubCode = xml.SubCode) AND (Table1.Id = Table3.Id)
     FOR XML PATH ('Details'),Type) as 'Table1',
FROM Users Table1
INNER JOIN CONTAINSTABLE(Users,[Name], @SearchKeys) AS KEY_TBL ON Id = KEY_TBL.[KEY]
INNER JOIN Regd Table3 ON Table3.Id = Table1.Id,
OPENXML (@idoc,'/Request/List',2)
WITH (SubCode NVARCHAR(20)) as xml
WHERE  (xml.SubCode = '' or Table3.SubCode = xml.SubCode) AND (Table3.Id = Table1.Id)
FOR XML PATH ('List')
UNION
SELECT
SELECT
Table1.Name 'Table1/Name',
Table1.Email 'Table1/Email',
(    SELECT
     Table2.Address 'Address',
     Table2.Phone 'Phone',
     FROM Details Table2
     INNER JOIN Regd Table3 ON Table3.Code = Table2.Code
     AND (Table3.SubCode = xml.SubCode) AND (Table1.Id = Table3.Id)
     FOR XML PATH ('Details'),Type) as 'Table1',
FROM Users Table1
INNER JOIN CONTAINSTABLE(Users,[Email], @SearchKeys) AS KEY_TBL ON Id = KEY_TBL.[KEY]
INNER JOIN Regd Table3 ON Table3.Id = Table1.Id,
OPENXML (@idoc,'/Request/List',2)
WITH (SubCode NVARCHAR(20)) as xml
WHERE  (xml.SubCode = '' or Table3.SubCode = xml.SubCode) AND (Table3.Id = Table1.Id)
FOR XML PATH ('List')

这里是我期望的输出

<List>
<Table1>
<Name></Name>
<Email></Email>
<Details>
<Address></Address>
<Phone></Phone>
</Details>
</Table1>
</List>

我认为请求XML参数在这里没有任何用处，因为它只是一个语法错误

检索结构化Xml从Sql Server Express 2005

你的代码中有很多语法错误，但我认为它们是存在的，因为你已经删除了很多你认为不必要的东西

你说你得到的错误是Incorrect syntax near the keyword 'union'。

这会给你那个错误，

select *
from YourTable
for xml path('list')
union
select *
from YourTable
for xml path('list')

你必须像这样在select语句中嵌入查询。

select
(select *
 from YourTable
 for xml path('list'))
union
select
(select *
 from YourTable
 for xml path('list'))

如果您希望列为XML类型，则需要添加type并使用union all，因为The xml data type cannot be selected as DISTINCT because it is not comparable. 

select
(select *
 from YourTable
 for xml path('list'), type)
union all
select
(select *
 from YourTable
 for xml path('list'), type)

我不知道这是否最终会给你你想要的输出，但这是Incorrect syntax near the keyword 'union'的原因，你可以做些什么。

相关文章：