XML Query Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

XML Query Issue

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2011-04-29 : 04:54:26

Hi,

Am framing XML query for the first time.And i face the following issue.Can anyone help me out of this.

My query is

select
b.invoicenumber as invoice,
(
select
a.errorcode1 as err1,
a.errorcode2 as err2

from
error a
where a.crn= b.crn
for xml auto
),
(select
c.internalnumber as Num
from
details c
where c.crn=b.crn

for xml auto)
from
Transacion b
for xml auto

The error which throws up is:

Unnamed tables cannot be used as xml identifers as well as unnamed columns cannot be used for attribute names.Name unnnamed tables/column using as in the select list.

I have named all the column/tables still am not sure why it throws the error

susan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-29 : 05:52:43

Which of the two correlated subqueries is throwing the error?

N 56°04'39.26"
E 12°55'05.63"

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2011-04-29 : 06:41:28

It is stating at this line
select
b.invoicenumber as invoice,--at this line

susan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-29 : 07:02:23

Yes, but the error occurs because the correlated subqueries are not named.
Also make sure inlcude "TYPE" keyword for correlated subqueries!

select
v.number as invoice,
(
select
a.number as err1,
2 * a.number as err2
from
master..spt_values as a
where a.number = v.number % 2
for xml auto, type
) AS y,
(select
c.number as Num
from
master..spt_values as c
where c.number = v.number + 10
for xml auto, type) AS x
from 
master..spt_values AS v
where type = 'p' and number between 10 and 19
for xml auto

N 56°04'39.26"
E 12°55'05.63"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 07:06:38

Do two things - see changes in red


SELECT
   b.invoicenumber AS invoice,
   (
       SELECT
          a.errorcode1 AS err1,
          a.errorcode2 AS err2
       FROM
          ERROR a
       WHERE
          a.crn = b.crn
          FOR XML AUTO ,TYPE
   ) AS ErrorCode , -- If you want to name your element as ErrorCode
   (
       SELECT
          c.internalnumber AS Num
       FROM
          details c
       WHERE
          c.crn = b.crn
          
          FOR XML AUTO ,TYPE
   ) AS InternalNumber  -- If you want to name your element as InternalNumber
FROM
   Transacion b
   FOR XML AUTO

The error message is telling you that you need to give a name to the result of the inner query. The name you give is going to be the name of the XML element in auto mode.

The ",TYPE" option is required because by default, the output from the inner query is nvarchar, but it needs to be XML for assembling it with the rest of the XML.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 07:07:12

Peso, you beat me by 3 minutes

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2011-04-29 : 07:35:19

Thank You Peso and sunita.
Both works fine

susan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources