Retrieve Structured Xml from Sql Server Express 2005

Question

I would like to have xml output from Full Text Search indexed tables like below. but my code generates incorrect syntax near union my code

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')

here is out put i expected to have

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

i think request xml parameter would not be of any use here as it is just a syntax error

Can you explain a bit what you're trying to do?? You're just throwing a huge complicated SQL statement at us, with no background, no information about the tables involved, their structure, their data etc. What e.g. is that @idoc you're using in two places... how does that get populated, from what data?? Could you maybe simplify this to a single SELECT and leave out the UNION part for now!?!? — marc_s
– marc_s, Commented Apr 19, 2011 at 12:20

Mikael Eriksson · Accepted Answer · 2011-04-19 13:11:15Z

1

You have a lot of syntax errors in your code but I assume they are there because you have removed a lot of stuff that you thought was not necessary.

The error you say you get is Incorrect syntax near the keyword 'union'.

This will give you that error,

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

You have to embed the queries in a select statement like this.

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

If you want the columns to be of XML type you need to add type and use union all because 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)

I have no idea if this eventually will give you the output you want but this is the reason for Incorrect syntax near the keyword 'union' and what you can do about it.

answered Apr 19, 2011 at 13:11

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Deeptechtons Over a year ago

+1 for explaining the why's of doing something solves the problem

Deeptechtons Over a year ago

Alert , how do i make it account for null values of columns it doesn't generate me a empty <Details></Details> xml element when the address , Phone elements are null/empty

Mikael Eriksson Over a year ago

@Deeptechtons – I think you have to ask a new question about this, including some sample code and expected output. When I test with null values I do get a empty Details tag that look like this <Details />

Deeptechtons Over a year ago

i completely agree here is the link to new question link

Collectives™ on Stack Overflow

Retrieve Structured Xml from Sql Server Express 2005

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related