从Sql Server Express 2005中检索结构化Xml

| 我想从全文搜索索引表中获取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参数在这里没有任何用处，因为它只是语法错误
2020-06-13 2 条评论
分享

没有找到相关结果

    已邀请:

    枫湃揩乾纲

    您的代码中有很多语法错误，但是我认为它们在那里，因为您删除了很多您认为不必要的东西。 您说得到的错误是
    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\'
    的原因以及您可以采取的措施。
    2020-06-13 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.050679922103882