For Xml Path, functions and UDF's

I have created a select statement to return using for xml path which works
without problem.
I now need to add into it the results of a UDF which relates to the data I
already have (parent - child stuff) but I'm running into problems doing this
.
If i include the complete sub query directly in the sql it works without any
issues, but if I take the sub select and put into into a table returning UDF
I get an error "Incorrect syntax near '.'." and the '.' is the parameter
passed to the UDF. The reason for looking at placing the sub-select in a UD
F
is that the same query needs to be included in 20+ sp's all returning Xml
using For Xml Path
I have included pseudo-SQL below showing examples
Anybody have any idea why the UDF won't work'
original sql that works
select a, b,c
from table1
for xml path ('item'), root('root')
modified that will work
select
t1.a,
t1.b,
t1.c,
(select t3.a, t2.e, t2.f
from table2 as t2
join table3 as t3 on t2.id = t3.id
where t3.a = t1.a
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
modified that won't work
select
t1.a,
t1.b,
t1.c,
(select u1.a, u1.e, u1.f
from dbo.UDF (t1.a) as u1
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')Ok, just found the issue with the UDF in BOL so I understand why I can't pas
s
the parameter in, which is a shame.
Currently investigating creating a scalar UDF that will return xml to see if
thay works.
"Nathan" wrote:

> I have created a select statement to return using for xml path which work
s
> without problem.
> I now need to add into it the results of a UDF which relates to the data I
> already have (parent - child stuff) but I'm running into problems doing th
is.
> If i include the complete sub query directly in the sql it works without a
ny
> issues, but if I take the sub select and put into into a table returning U
DF
> I get an error "Incorrect syntax near '.'." and the '.' is the parameter
> passed to the UDF. The reason for looking at placing the sub-select in a
UDF
> is that the same query needs to be included in 20+ sp's all returning Xml
> using For Xml Path
> I have included pseudo-SQL below showing examples
> Anybody have any idea why the UDF won't work'
> original sql that works
> select a, b,c
> from table1
> for xml path ('item'), root('root')
> modified that will work
> select
> t1.a,
> t1.b,
> t1.c,
> (select t3.a, t2.e, t2.f
> from table2 as t2
> join table3 as t3 on t2.id = t3.id
> where t3.a = t1.a
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
>
> modified that won't work
> select
> t1.a,
> t1.b,
> t1.c,
> (select u1.a, u1.e, u1.f
> from dbo.UDF (t1.a) as u1
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')|||Well after dispairing that I could resolve the issue and having to post on
the forum I've sorted it.
Final solution was to use a scalar UDF that took a parameter from the select
and returned a xml data type with the data in it which was then amalgamated
by For Xml Path correctly.
"Nathan" wrote:
> Ok, just found the issue with the UDF in BOL so I understand why I can't p
ass
> the parameter in, which is a shame.
> Currently investigating creating a scalar UDF that will return xml to see
if
> thay works.
> "Nathan" wrote:
>

For Xml Path, functions and UDF's

I have created a select statement to return using for xml path which works
without problem.
I now need to add into it the results of a UDF which relates to the data I
already have (parent - child stuff) but I'm running into problems doing this.
If i include the complete sub query directly in the sql it works without any
issues, but if I take the sub select and put into into a table returning UDF
I get an error "Incorrect syntax near '.'." and the '.' is the parameter
passed to the UDF. The reason for looking at placing the sub-select in a UDF
is that the same query needs to be included in 20+ sp's all returning Xml
using For Xml Path
I have included pseudo-SQL below showing examples
Anybody have any idea why the UDF won't work?
original sql that works
select a, b,c
from table1
for xml path ('item'), root('root')
modified that will work
select
t1.a,
t1.b,
t1.c,
(select t3.a, t2.e, t2.f
from table2 as t2
join table3 as t3 on t2.id = t3.id
where t3.a = t1.a
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
modified that won't work
select
t1.a,
t1.b,
t1.c,
(select u1.a, u1.e, u1.f
from dbo.UDF (t1.a) as u1
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
Ok, just found the issue with the UDF in BOL so I understand why I can't pass
the parameter in, which is a shame.
Currently investigating creating a scalar UDF that will return xml to see if
thay works.
"Nathan" wrote:

> I have created a select statement to return using for xml path which works
> without problem.
> I now need to add into it the results of a UDF which relates to the data I
> already have (parent - child stuff) but I'm running into problems doing this.
> If i include the complete sub query directly in the sql it works without any
> issues, but if I take the sub select and put into into a table returning UDF
> I get an error "Incorrect syntax near '.'." and the '.' is the parameter
> passed to the UDF. The reason for looking at placing the sub-select in a UDF
> is that the same query needs to be included in 20+ sp's all returning Xml
> using For Xml Path
> I have included pseudo-SQL below showing examples
> Anybody have any idea why the UDF won't work?
> original sql that works
> select a, b,c
> from table1
> for xml path ('item'), root('root')
> modified that will work
> select
> t1.a,
> t1.b,
> t1.c,
> (select t3.a, t2.e, t2.f
> from table2 as t2
> join table3 as t3 on t2.id = t3.id
> where t3.a = t1.a
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
>
> modified that won't work
> select
> t1.a,
> t1.b,
> t1.c,
> (select u1.a, u1.e, u1.f
> from dbo.UDF (t1.a) as u1
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
|||Well after dispairing that I could resolve the issue and having to post on
the forum I've sorted it.
Final solution was to use a scalar UDF that took a parameter from the select
and returned a xml data type with the data in it which was then amalgamated
by For Xml Path correctly.
"Nathan" wrote:
[vbcol=seagreen]
> Ok, just found the issue with the UDF in BOL so I understand why I can't pass
> the parameter in, which is a shame.
> Currently investigating creating a scalar UDF that will return xml to see if
> thay works.
> "Nathan" wrote:

for xml path reverse?

just started playing with xml datatype, and I'm running into a performance issue...

I have to query an xml column which was populated by a 'for xml path' statement, and get the values back into relational tables...

select
DeletedData.value('(/row/ListingID)[1]','int') as ListingID,
DeletedData.value('(/row/ListingTypeID)[1]','int') as ListingTypeID, DeletedData.value('(/row/EventID)[1]','int') as EventID,
DeletedData.value('(/row/UserID)[1]','uniqueidentifier') as
etc.......
............
............
where DeletedData.value('(/row/ListingID)[1]','int') = x

Performance slows down considerably as the number of values retreived in the select increases which is understandable since it looks like it traverses for every value...

Is there a way to do a 'for xml path' reverse into a table variable without explicitly retreiving every value?

thanks.Do you have an XML Index? If so, what secondary XML Indexes do you have?

There are a couple of things you can try doing.

Is your data untyped (meaning there is no associated XML Schema Collection)? If so, then you should rewrite your path expressions to look like this:
(/row/ListingID/text())[1]

Also, I would recommend changing your where clause to use the XML datatype exist() method, this will maximize the effectiveness of your XML Indexes.
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1
|||

Can you give a better repro? Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

Also, as a performance hint: You may want to use

where 1= col.exist('/row/ListingID/text()[. = sql:column("x")]')

which can give you better performance than doing the cast into SQL and then the comparison.

Best regards
Michael

|||hi..

rewriting the expression as (/row/ListingID/text())[1] improved performance by about 25%...

changing the where clause to
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1 didn't make any difference...

adding a for path index made very little difference ( < 5%)

CREATE PRIMARY XML INDEX idx_DeletedData on audit (DeletedData)
CREATE XML INDEX idx_DeletedDataPath on audit (DeletedData) USING XML INDEX idx_DeletedData FOR PATH

Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

we have generic data audit triggers that look like this:

insert audit select tablename, (select * for xml path from inserted), (select * from deleted for xml path).... etc.

the select described above is used to get the audit trail of changes to a row.

we have a large development effort going on, and using genric triggers seemed like a perfect way to audit data in an enviroment where number of tables and table schema changes on a daily basis without having to change triggers and audit tables... Once we stabilize the schema we might move to a more sophisticated strategy.. I'd prefer not to since I really like this solution, but if getting an audit tral of 100 rows takes 20-30 seconds, i might have to...

thanks!|||Thanks for testing it. Did you try the WHERE clause rewrite with the PATH index together?

If so, and you have a reasonable amount of data, can you please contact me in email (mrys at the usual microsoft com domain).

Thanks
Michael|||How selective is the variable @.X? If it is highly selective, then you may want to consider also creating a VALUE index on the XML Index. This will allow QO to select a plan in which we seek for the value and then match the path.|||

Take a look at the optimization described under "Merging multiple value() method executions for indexed XML" in the XML optimizations whitepaper at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqloptxml.asp.

The optimization can apply to your case:

1) When it is written as nodes()/value() combination
2) You use attributes instead of subelements (of <row>). If this is an option, please rerun the experiments and let us know the performance you observe.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

|||PugV

Let me know if you still have the performance issue. There's a way to get close to what you want with a better performance. The best is to write to Eugene dot Kogan at Microsoft dot com and I'll reply to the forum.

Best regards,
Eugene Kogan
Technical Lead,
Microsoft SQL Server
|||sorry guys, was away for a while, I will do some more benchmarking next week and get back to you.
thanks a lot for everyone's help!

for xml path reverse?

just started playing with xml datatype, and I'm running into a performance issue...

I have to query an xml column which was populated by a 'for xml path' statement, and get the values back into relational tables...

select
DeletedData.value('(/row/ListingID)[1]','int') as ListingID,
DeletedData.value('(/row/ListingTypeID)[1]','int') as ListingTypeID, DeletedData.value('(/row/EventID)[1]','int') as EventID,
DeletedData.value('(/row/UserID)[1]','uniqueidentifier') as
etc.......
............
............
where DeletedData.value('(/row/ListingID)[1]','int') = x

Performance slows down considerably as the number of values retreived in the select increases which is understandable since it looks like it traverses for every value...

Is there a way to do a 'for xml path' reverse into a table variable without explicitly retreiving every value?

thanks.Do you have an XML Index? If so, what secondary XML Indexes do you have?

There are a couple of things you can try doing.

Is your data untyped (meaning there is no associated XML Schema Collection)? If so, then you should rewrite your path expressions to look like this:
(/row/ListingID/text())[1]

Also, I would recommend changing your where clause to use the XML datatype exist() method, this will maximize the effectiveness of your XML Indexes.
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1
|||

Can you give a better repro? Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

Also, as a performance hint: You may want to use

where 1= col.exist('/row/ListingID/text()[. = sql:column("x")]')

which can give you better performance than doing the cast into SQL and then the comparison.

Best regards
Michael

|||hi..

rewriting the expression as (/row/ListingID/text())[1] improved performance by about 25%...

changing the where clause to
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1 didn't make any difference...

adding a for path index made very little difference ( < 5%)

CREATE PRIMARY XML INDEX idx_DeletedData on audit (DeletedData)
CREATE XML INDEX idx_DeletedDataPath on audit (DeletedData) USING XML INDEX idx_DeletedData FOR PATH

Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

we have generic data audit triggers that look like this:

insert audit select tablename, (select * for xml path from inserted), (select * from deleted for xml path).... etc.

the select described above is used to get the audit trail of changes to a row.

we have a large development effort going on, and using genric triggers seemed like a perfect way to audit data in an enviroment where number of tables and table schema changes on a daily basis without having to change triggers and audit tables... Once we stabilize the schema we might move to a more sophisticated strategy.. I'd prefer not to since I really like this solution, but if getting an audit tral of 100 rows takes 20-30 seconds, i might have to...

thanks!|||Thanks for testing it. Did you try the WHERE clause rewrite with the PATH index together?

If so, and you have a reasonable amount of data, can you please contact me in email (mrys at the usual microsoft com domain).

Thanks
Michael|||How selective is the variable @.X? If it is highly selective, then you may want to consider also creating a VALUE index on the XML Index. This will allow QO to select a plan in which we seek for the value and then match the path.|||

Take a look at the optimization described under "Merging multiple value() method executions for indexed XML" in the XML optimizations whitepaper at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqloptxml.asp.

The optimization can apply to your case:

1) When it is written as nodes()/value() combination
2) You use attributes instead of subelements (of <row>). If this is an option, please rerun the experiments and let us know the performance you observe.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

|||PugV

Let me know if you still have the performance issue. There's a way to get close to what you want with a better performance. The best is to write to Eugene dot Kogan at Microsoft dot com and I'll reply to the forum.

Best regards,
Eugene Kogan
Technical Lead,
Microsoft SQL Server
|||sorry guys, was away for a while, I will do some more benchmarking next week and get back to you.
thanks a lot for everyone's help!

FOR XML PATH Question - Nesting Elements

Hi,
I was wondering if anyone can please help me?...I am trying to produce an
XML file using the new PATH function in SQL 2005 that has 'bullet' nodes
nested as childs of a 'bullets' element. Each bullet (to a maximum of 10)
is represented by a field in the database that is named as follows;
field_b1, field_b2, field_b3, ....etc to field_b10
I am using the below statement to produce the XML which currently only works
when I only specify 1 attribute value eg. <bullets><bullet
id="1">Parking</bullet><bullets>;
select top 1
field_id as '@.id',
field_name as 'address/name',
field_street as 'address/street',
field_town as 'address/town',
field_county as 'address/county',
field_pc as 'address/postcode',
field_price as 'price/@.value',
field_stat as 'price/status',
field_pq as 'price/qualifier',
1 as 'bullets/bullet/@.id',
field_b1 as 'bullets/bullet'
from data
where field_id = 9999999
for xml path('property'), root('info')
Which produces;
<info>
<property id="9999999">
<address>
<.... />
<.... />
etc
</address>
<price value="999999">
<... />
<... />
</price>
<bullets>
<bullet id="1">Converted Flat</bullet>
</bullets>
</property>
</info>
If I try to add ;
2 as 'bullets/bullet/@.id',
field_b2 as 'bullets/bullet'
to my statement to create the nested node with a different ID and value it
does not work. Does anyone know of a work around / solution?
Many thanks,
Pete
If I understood your problem correctly something like below should work for
you:
SELECT
1 as "bulets/bulet",
NULL as "bulets/dummy_elt",
2 as "bulets/bulet",
NULL as "bulets/dummy_elt",
3 as "bulets/bulet"
FOR XML PATH('many_bullets')
The NULL columns break the FOR XML PATH groupping logic.
This only works if you don't have "ELEMENTS XSINIL" FOR XML directive.
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:e7%23ZgLycFHA.2688@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I was wondering if anyone can please help me?...I am trying to produce an
> XML file using the new PATH function in SQL 2005 that has 'bullet' nodes
> nested as childs of a 'bullets' element. Each bullet (to a maximum of
> 10) is represented by a field in the database that is named as follows;
> field_b1, field_b2, field_b3, ....etc to field_b10
> I am using the below statement to produce the XML which currently only
> works when I only specify 1 attribute value eg. <bullets><bullet
> id="1">Parking</bullet><bullets>;
> select top 1
> field_id as '@.id',
> field_name as 'address/name',
> field_street as 'address/street',
> field_town as 'address/town',
> field_county as 'address/county',
> field_pc as 'address/postcode',
> field_price as 'price/@.value',
> field_stat as 'price/status',
> field_pq as 'price/qualifier',
> 1 as 'bullets/bullet/@.id',
> field_b1 as 'bullets/bullet'
> from data
> where field_id = 9999999
> for xml path('property'), root('info')
> Which produces;
> <info>
> <property id="9999999">
> <address>
> <.... />
> <.... />
> etc
> </address>
> <price value="999999">
> <... />
> <... />
> </price>
> <bullets>
> <bullet id="1">Converted Flat</bullet>
> </bullets>
> </property>
> </info>
> If I try to add ;
> 2 as 'bullets/bullet/@.id',
> field_b2 as 'bullets/bullet'
> to my statement to create the nested node with a different ID and value it
> does not work. Does anyone know of a work around / solution?
> Many thanks,
> Pete
>
>
|||Another solution is to make the bullet generation a subquery of its own (if
you do not know a priori how many you may have).
Best regards
Michael
"Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
news:OYVV8d6cFHA.2520@.TK2MSFTNGP09.phx.gbl...
> If I understood your problem correctly something like below should work
> for you:
> SELECT
> 1 as "bulets/bulet",
> NULL as "bulets/dummy_elt",
> 2 as "bulets/bulet",
> NULL as "bulets/dummy_elt",
> 3 as "bulets/bulet"
> FOR XML PATH('many_bullets')
> The NULL columns break the FOR XML PATH groupping logic.
> This only works if you don't have "ELEMENTS XSINIL" FOR XML directive.
> Regards,
> Eugene
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Pete Roberts" <peter.roberts@.vebra.com> wrote in message
> news:e7%23ZgLycFHA.2688@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks for your help, the solutions offered are exactly what I was after!
Pete
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Od9QvEgdFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Another solution is to make the bullet generation a subquery of its own
> (if you do not know a priori how many you may have).
> Best regards
> Michael
> "Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
> news:OYVV8d6cFHA.2520@.TK2MSFTNGP09.phx.gbl...
>

For XML Path problems

Can anyone tell me how to add multiple columns with the same name? Here is an example of the XML format I'm trying to create using For XML Path

<TABLETYPE TYPEABBRV=“IDEADISAB” TOTALINDICATOR=“N”>
<CATEGORY TYPE=“DISABCATIDEA” VALUE=“AUT”/>
<CATEGORY TYPE=“AGESA” VALUE=“6”/>
<CATEGORY TYPE=“EDENVIRIDEASA” VALUE=“RC80”/>
<AMOUNT>10</AMOUNT>
</TABLETYPE>

Here is the query I was trying to use

SELECT

'IDEADISAB' AS '@.TYPEABBRV',

'N' AS '@.TOTALINDICATOR',

'DISABCATIDEA' AS 'CATEGORY/@.TYPE',

IdeaCategory AS 'CATEGORY/@.VALUE',

'AGESA' AS 'CATEGORY/@.TYPE',

AGE AS 'CATEGORY/@.VALUE',

'EDENVIRIDESAS' AS 'CATEGORY/@.TYPE',

EECATEGORY AS 'CATEGORY/@.VALUE',

COUNT(*) AS 'AMOUNT'

FROM EdenIdeaStudents group by Age, EeCategory, IdeaCategory

FOR XML PATH('TABLETYPE'), TYPE)

And this is the error I'm getting

Msg 6810, Level 16, State 1, Line 1

Column name 'CATEGORY/@.TYPE' is repeated. The same attribute cannot be generated more than once on the same XML tag.

Any help would be much appreciated

Use subqueries

SELECT
'IDEADISAB' AS '@.TYPEABBRV',
'N' AS '@.TOTALINDICATOR',
(SELECT
'DISABCATIDEA' AS '@.TYPE',
IdeaCategory AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
(SELECT
'AGESA' AS '@.TYPE',
AGE AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
(SELECT
'EDENVIRIDESAS' AS '@.TYPE',
EECATEGORY AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
COUNT(*) AS 'AMOUNT'
FROM EdenIdeaStudents group by Age, EeCategory, IdeaCategory
FOR XML PATH('TABLETYPE'), TYPE

|||

Thanks Mark,

I had already tried using subqueries but could not quite get the syntax correct. I was using the 'from' statement after every select and that was throwing all of my records under one tag. Thanks again.

For XML Path problem?

I have a stored procedure that is to create an XML file, once the temporary
table is created with the recordset I wish to call the results as per below
query -
select mailid,
addresstypeid,
mailtexttypeid,
registereduserid,
emailaddressid,
attachmentid,
emailpriority,
emailsubject,
fromemail,
emailbody,
mailaction,
createddate,
emaildate
from #EmailHeaderXMLOutput
for xml path ('row'), root('root')
I keep getting an error message saying:
Line 168: Incorrect syntax near 'path'.
What is wrong with this query? The reason I am doing it this way is that I
need to have a root node as well as a row node before the actual data.Daniel Badger wrote:
> I have a stored procedure that is to create an XML file, once the temporar
y
> table is created with the recordset I wish to call the results as per belo
w
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query?
I don't see anything wrong with that snippet, unless you are using SQL
Server 2000 which does not support the root clause I think as it is a
new feature only supported in SQL server 2005.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||And it certainly doesn't support FOR PATH.
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:uJYgjngmHHA.960@.TK2MSFTNGP03.phx.gbl...
> Daniel Badger wrote:
> I don't see anything wrong with that snippet, unless you are using SQL
> Server 2000 which does not support the root clause I think as it is a new
> feature only supported in SQL server 2005.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/|||Are you using SQL Server 2000 or 2005?
As others have mentioned, you need 2005 for FOR XML PATH to work.
however for this simple query, you could use FOR XML RAW in 2000 and use the
client-side capabilities to add the root node (all providers have the
ability to set a root node property on the SQLXML provider).
Best regards
Michael
"Daniel Badger" <DanielBadger@.discussions.microsoft.com> wrote in message
news:570058BA-F3A6-4405-8CF6-42E5F9D09A80@.microsoft.com...
>I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per
> below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query? The reason I am doing it this way is that I
> need to have a root node as well as a row node before the actual data.

For XML Path problem?

I have a stored procedure that is to create an XML file, once the temporary
table is created with the recordset I wish to call the results as per below
query -
select mailid,
addresstypeid,
mailtexttypeid,
registereduserid,
emailaddressid,
attachmentid,
emailpriority,
emailsubject,
fromemail,
emailbody,
mailaction,
createddate,
emaildate
from #EmailHeaderXMLOutput
for xml path ('row'), root('root')
I keep getting an error message saying:
Line 168: Incorrect syntax near 'path'.
What is wrong with this query? The reason I am doing it this way is that I
need to have a root node as well as a row node before the actual data.
Daniel Badger wrote:
> I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query?
I don't see anything wrong with that snippet, unless you are using SQL
Server 2000 which does not support the root clause I think as it is a
new feature only supported in SQL server 2005.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Are you using SQL Server 2000 or 2005?
As others have mentioned, you need 2005 for FOR XML PATH to work.
however for this simple query, you could use FOR XML RAW in 2000 and use the
client-side capabilities to add the root node (all providers have the
ability to set a root node property on the SQLXML provider).
Best regards
Michael
"Daniel Badger" <DanielBadger@.discussions.microsoft.com> wrote in message
news:570058BA-F3A6-4405-8CF6-42E5F9D09A80@.microsoft.com...
>I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per
> below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query? The reason I am doing it this way is that I
> need to have a root node as well as a row node before the actual data.

FOR XML PATH NULL Element

Hi there,
I'm using sp with FOR XML PATH('Employee'), ELEMENTS to return XML Data
from SQL Server 2005.
If row return null value return xml does not return element.
Can it be returned xml element even it contains null?
Ex
i'm getting this
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
</employee>
i want this:)
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
<image/>
</employee>
*** Sent via Developersdex http://www.examnotes.net ***Hello Zoka,
You could do something like this:
SELECT
..
e.image AS "image/node()"
,'' AS "image/node()" -- Same as above :-), now "image/node()" is never
NULL :-)
..
FROM ... AS e
FOR XML PATH('employee')
HTH
/ Tobias|||
Hi there,
I tried this functionality but does not solve the problem.
*** Sent via Developersdex http://www.examnotes.net ***|||
Sorry Tobias,
This solves my problem, thanks:))
I haven't drink coffe when i first try the script:)
Regards,
Zoka
*** Sent via Developersdex http://www.examnotes.net ***

FOR XML PATH nested query refer to parent variable

I have the following query:
Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
(Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
FROM ods_TransmitFile WHERE BunCode = 1821
FOR XML PATH, TYPE)
FROM ods_TransmitFile
GROUP BY BunCode
ORDER BY BunCode
FOR XML PATH('BunCode'), ROOT('DM')
The query returns the correct structure however I would like the nested
query to refer to the '@.BunCode' of the parent query.
At present the nested results will always be for BunCode 1821. How can
I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
element?
Hope this makes sense?
Maybe by aliasing the tables? NOTE: *Untested*:
Select otf2.BunCode as '@.BunCode', Min(otf2.TradingDate) as '@.TradingDate',
(Select otf1.ItemQty, otf1.TransactionSequenceNumber, otf1.GsiCode,
otf1.RetalItemCode
FROM ods_TransmitFile otf1 WHERE otf1.BunCode = otf2.BunCode
FOR XML PATH, TYPE)
FROM ods_TransmitFile otf2
GROUP BY otf2.BunCode
ORDER BY otf2.BunCode
FOR XML PATH('BunCode'), ROOT('DM')
<justin.drerup@.gmail.com> wrote in message
news:1166543884.982800.201900@.t46g2000cwa.googlegr oups.com...
>I have the following query:
> Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
> (Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
> FROM ods_TransmitFile WHERE BunCode = 1821
> FOR XML PATH, TYPE)
> FROM ods_TransmitFile
> GROUP BY BunCode
> ORDER BY BunCode
> FOR XML PATH('BunCode'), ROOT('DM')
> The query returns the correct structure however I would like the nested
> query to refer to the '@.BunCode' of the parent query.
> At present the nested results will always be for BunCode 1821. How can
> I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
> element?
> Hope this makes sense?
>
|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.
|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.

FOR XML PATH nested query refer to parent variable

I have the following query:
Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
(Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
FROM ods_TransmitFile WHERE BunCode = 1821
FOR XML PATH, TYPE)
FROM ods_TransmitFile
GROUP BY BunCode
ORDER BY BunCode
FOR XML PATH('BunCode'), ROOT('DM')
The query returns the correct structure however I would like the nested
query to refer to the '@.BunCode' of the parent query.
At present the nested results will always be for BunCode 1821. How can
I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
element?
Hope this makes sense?Maybe by aliasing the tables? NOTE: *Untested*:
Select otf2.BunCode as '@.BunCode', Min(otf2.TradingDate) as '@.TradingDate',
(Select otf1.ItemQty, otf1.TransactionSequenceNumber, otf1.GsiCode,
otf1.RetalItemCode
FROM ods_TransmitFile otf1 WHERE otf1.BunCode = otf2.BunCode
FOR XML PATH, TYPE)
FROM ods_TransmitFile otf2
GROUP BY otf2.BunCode
ORDER BY otf2.BunCode
FOR XML PATH('BunCode'), ROOT('DM')
<justin.drerup@.gmail.com> wrote in message
news:1166543884.982800.201900@.t46g2000cwa.googlegroups.com...
>I have the following query:
> Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
> (Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
> FROM ods_TransmitFile WHERE BunCode = 1821
> FOR XML PATH, TYPE)
> FROM ods_TransmitFile
> GROUP BY BunCode
> ORDER BY BunCode
> FOR XML PATH('BunCode'), ROOT('DM')
> The query returns the correct structure however I would like the nested
> query to refer to the '@.BunCode' of the parent query.
> At present the nested results will always be for BunCode 1821. How can
> I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
> element?
> Hope this makes sense?
>|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.

FOR XML PATH + file structure

Hey there,
I used the FOR XML PATH feature but the XML result is in only one line,
it does not conserve the XML structure (treeview). When I open the XML
file with IE, it s working very (I have the XML hierarchy) but when I
use Visual Studio or other tool to edit it, I ve just one line!
For example:
<children group=""><child firstname="" lastname=""></child>=""><child
firstname=""
lastname=""></child>.......................</children>.........
And I d like to have
<children group="">
<child firstname="" lastname=""></child>="">
<child firstname="" lastname=""></child>
......................
</children>
..........
To give you an example of the query:
SELECT Child.group AS '@.group',
(SELECT firstname AS '@.firstname',
lastname AS '@.lastname'
FROM Collecte_Data_Extract cde
FOR XML PATH('child'), TYPE)
FROM Feed AS Child
FOR XML PATH('children'), TYPE
Any idea?
Many thanks in advanceThe structure is "conserved"; the whitespace is not. Actually what's
happening is IE is adding whitespace (linefeeds and tabs) to display your
XML. If you want to format your XML in Visual Studio, create a new XML
file, copy and paste your XML into it, and select Edit > Advanced > Format
from the main menu. The extra linefeeds and tabs are irrelevant to your XML
parser (or at least should be).
"Vins" <vbellet@.gmail.com> wrote in message
news:1165406662.637039.25890@.f1g2000cwa.googlegroups.com...
> Hey there,
> I used the FOR XML PATH feature but the XML result is in only one line,
> it does not conserve the XML structure (treeview). When I open the XML
> file with IE, it s working very (I have the XML hierarchy) but when I
> use Visual Studio or other tool to edit it, I ve just one line!
>
> For example:
> <children group=""><child firstname="" lastname=""></child>=""><child
> firstname=""
> lastname=""></child>.......................</children>.........
>
> And I d like to have
> <children group="">
> <child firstname="" lastname=""></child>="">
> <child firstname="" lastname=""></child>
> .......................
> </children>
> ..........
>
> To give you an example of the query:
>
> SELECT Child.group AS '@.group',
>
> (SELECT firstname AS '@.firstname',
> lastname AS '@.lastname'
>
> FROM Collecte_Data_Extract cde
>
> FOR XML PATH('child'), TYPE)
>
> FROM Feed AS Child
> FOR XML PATH('children'), TYPE
>
> Any idea?
> Many thanks in advance
>|||In fact the idea is to have the result dynamically.
For example if I choose to open this XML file in NotePad it does work|||"Vins" <vbellet@.gmail.com> wrote in message
news:1165833640.839620.293150@.16g2000cwy.googlegroups.com...
> In fact the idea is to have the result dynamically.
> For example if I choose to open this XML file in NotePad it does work
Huh?

FOR XML PATH + file structure

Hey there,
I used the FOR XML PATH feature but the XML result is in only one line,
it does not conserve the XML structure (treeview). When I open the XML
file with IE, it s working very (I have the XML hierarchy) but when I
use Visual Studio or other tool to edit it, I ve just one line!
For example:
<children group=""><child firstname="" lastname=""></child>=""><child
firstname=""
lastname=""></child>.......................</children>.........
And I d like to have
<children group="">
<child firstname="" lastname=""></child>="">
<child firstname="" lastname=""></child>
.......................
</children>
...........
To give you an example of the query:
SELECT Child.group AS '@.group',
(SELECT firstname AS '@.firstname',
lastname AS '@.lastname'
FROM Collecte_Data_Extract cde
FOR XML PATH('child'), TYPE)
FROM Feed AS Child
FOR XML PATH('children'), TYPE
Any idea?
Many thanks in advance
The structure is "conserved"; the whitespace is not. Actually what's
happening is IE is adding whitespace (linefeeds and tabs) to display your
XML. If you want to format your XML in Visual Studio, create a new XML
file, copy and paste your XML into it, and select Edit > Advanced > Format
from the main menu. The extra linefeeds and tabs are irrelevant to your XML
parser (or at least should be).
"Vins" <vbellet@.gmail.com> wrote in message
news:1165406662.637039.25890@.f1g2000cwa.googlegrou ps.com...
> Hey there,
> I used the FOR XML PATH feature but the XML result is in only one line,
> it does not conserve the XML structure (treeview). When I open the XML
> file with IE, it s working very (I have the XML hierarchy) but when I
> use Visual Studio or other tool to edit it, I ve just one line!
>
> For example:
> <children group=""><child firstname="" lastname=""></child>=""><child
> firstname=""
> lastname=""></child>.......................</children>.........
>
> And I d like to have
> <children group="">
> <child firstname="" lastname=""></child>="">
> <child firstname="" lastname=""></child>
> .......................
> </children>
> ..........
>
> To give you an example of the query:
>
> SELECT Child.group AS '@.group',
>
> (SELECT firstname AS '@.firstname',
> lastname AS '@.lastname'
>
> FROM Collecte_Data_Extract cde
>
> FOR XML PATH('child'), TYPE)
>
> FROM Feed AS Child
> FOR XML PATH('children'), TYPE
>
> Any idea?
> Many thanks in advance
>
|||In fact the idea is to have the result dynamically.
For example if I choose to open this XML file in NotePad it does work
|||"Vins" <vbellet@.gmail.com> wrote in message
news:1165833640.839620.293150@.16g2000cwy.googlegro ups.com...
> In fact the idea is to have the result dynamically.
> For example if I choose to open this XML file in NotePad it does work
Huh?

FOR XML PATH + file structure

Hey there,

I used the FOR XML PATH feature but the XML result is in only one line,
it does not conserve the XML structure (treeview). When I open the XML
file with IE, it s working very (I have the XML hierarchy) but when I
use Visual Studio or other tool to edit it, I ve just one line!

For example:
<children group=""><child firstname="" lastname=""></child>=""><child
firstname=""
lastname=""></child>.......................</children>.........

And I d like to have
<children group="">
<child firstname="" lastname=""></child>="">
<child firstname="" lastname=""></child>
.......................
</children>
...........

To give you an example of the query:

SELECTChild.group AS '@.group',

(SELECT firstnameAS '@.firstname',
lastnameAS '@.lastname'

FROM Collecte_Data_Extract cde

FOR XML PATH('child'), TYPE)

FROM Feed AS Child
FOR XML PATH('children'), TYPE

Any idea?
Many thanks in advanceVins (vbellet@.gmail.com) writes:

Quote:

Originally Posted by

I used the FOR XML PATH feature but the XML result is in only one line,
it does not conserve the XML structure (treeview). When I open the XML
file with IE, it s working very (I have the XML hierarchy) but when I
use Visual Studio or other tool to edit it, I ve just one line!

Trying running the query in Mgmt Studio, and then double-click on the
XML document to open it the XML viewer. If you save from the viewer
you get the format you are looking for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland but I want to do this dynamically, it s boring to do this
for each file (it s a daily report)

Other idea?|||Vins (vbellet@.gmail.com) writes:

Quote:

Originally Posted by

Thanks Erland but I want to do this dynamically, it s boring to do this
for each file (it s a daily report)

If it's a daily report, I assume that you run the program from a client.
Then I guess there is a suitable API that you can use to format your XML.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

FOR XML PATH

Hi,
I have the following query:
SELECT
C.CompanyName,
O.OrderDate,
E.LastName AS 'Registered By'
FROM Customers C
JOIN Orders O
ON C.CustomerID=O.CustomerID
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
ORDER BY C.CompanyName
I need all information of orders for particular company, appear under the
element of that company but the query that I have written returns the name
of company again and again for each order:
SELECT
C.CompanyName AS "@.Company",
O.OrderDate AS "Order/OrderDate",
E.LastName AS "Order/RegisteredBy"
FROM Customers C
JOIN Orders O
ON C.CustomerID=O.CustomerID
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
ORDER BY C.CompanyName
FOR XML PATH
Any help would be greatly appreciated.
Leila
oops! seems that the problem solved:
SELECT
C.CompanyName AS "@.Company",
(SELECT
O.OrderDate AS 'Order/OrderDate',
E.LastName AS 'Order/RegisteredBy'
FROM Orders O
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
WHERE O.CustomerID=C.CUstomerID
FOR XML PATH(''),type)
FROM Customers C
ORDER BY C.CompanyName
FOR XML PATH('Customers'),ROOT('ROOT')
Thanks to BOL documentation team :-)
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23AjU8w2IGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following query:
> SELECT
> C.CompanyName,
> O.OrderDate,
> E.LastName AS 'Registered By'
> FROM Customers C
> JOIN Orders O
> ON C.CustomerID=O.CustomerID
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> ORDER BY C.CompanyName
>
> I need all information of orders for particular company, appear under the
> element of that company but the query that I have written returns the name
> of company again and again for each order:
> SELECT
> C.CompanyName AS "@.Company",
> O.OrderDate AS "Order/OrderDate",
> E.LastName AS "Order/RegisteredBy"
> FROM Customers C
> JOIN Orders O
> ON C.CustomerID=O.CustomerID
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> ORDER BY C.CompanyName
> FOR XML PATH
> Any help would be greatly appreciated.
> Leila
>
>
|||Good documentation can help ;)
You can make your query slightly easier to maintain by writing the subquery
as
(SELECT
O.OrderDate AS 'OrderDate',
E.LastName AS 'RegisteredBy'
FROM Orders O
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
WHERE O.CustomerID=C.CUstomerID
FOR XML PATH('Order'),type)
Best regards
Michael
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23LnD5B3IGHA.3696@.TK2MSFTNGP15.phx.gbl...
> oops! seems that the problem solved:
> SELECT
> C.CompanyName AS "@.Company",
> (SELECT
> O.OrderDate AS 'Order/OrderDate',
> E.LastName AS 'Order/RegisteredBy'
> FROM Orders O
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> WHERE O.CustomerID=C.CUstomerID
> FOR XML PATH(''),type)
> FROM Customers C
> ORDER BY C.CompanyName
> FOR XML PATH('Customers'),ROOT('ROOT')
> Thanks to BOL documentation team :-)
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23AjU8w2IGHA.1676@.TK2MSFTNGP09.phx.gbl...
>

For XML Path

im using the ROOT directive in association with FOR XML PATH to return results. When the query returns no records then I get no root node either (which makes the XML invalid). Is there an attribute that specifies that the root node should always be returned (even when empty)?

i.e. <ROOT />

thx

Not that I know of. Since no rows were returned, not data would be returned at all. You could do something like this:

select cast(
'<root>' +

coalesce((select *
from sys.objects
where 1=2 --change to 1=1 to get rows
for xml path),'')

+ '</root>' as xml)

This does seem to work, though not 100% sure if there will be much of a performance hit.

|||

thanks for the tip :)

im am confused becuase if i say that i want a resultset typed as xml, then i would expect (for the xml to be valid) that it has a root node regardless..... what concept am i missing if this is not the case?

|||I think the fact is, it isn't invalid XML, it is nothing. So if you return no data, then no XML is created.|||

i agree

BUT :)

that does mean that anything that uses the resultset requires a condition to check if it is Null and either 1)do nothing, or 2) subsitute it for what would be valid xml i.e. an empty parent node eg <Root />

i think a lot of applications would require number 2, and therefore think the XML functionality of sql2005 should have this built in.

|||

And I don't disagree with you, though you can use the cast and concatenation thing I posted earlier as a workaround.

If noone posts that I was wrong, consider posting your suggestion here: https://connect.microsoft.com/SQLServer/Feedback and then post in this thread that you have, and I will vote for it.

|||

how about this:-

CREATE PROCEDURE [dbo].[up_DoStuff]
(
-- params
)
AS
SET NOCOUNT ON;

DECLARE @.pXML XML
SET @.pXML = (
SELECT
...
FROM
...
WHERE
...
FOR
XML Path('Test'),
ELEMENTS,
ROOT('Tests'),
TYPE
)

SELECT ISNULL(@.pXML, '<Tests/>')

For XML Path

im using the ROOT directive in association with FOR XML PATH to return results. When the query returns no records then I get no root node either (which makes the XML invalid). Is there an attribute that specifies that the root node should always be returned (even when empty)?

i.e. <ROOT />

thx

Not that I know of. Since no rows were returned, not data would be returned at all. You could do something like this:

select cast(
'<root>' +

coalesce((select *
from sys.objects
where 1=2 --change to 1=1 to get rows
for xml path),'')

+ '</root>' as xml)

This does seem to work, though not 100% sure if there will be much of a performance hit.

|||

thanks for the tip :)

im am confused becuase if i say that i want a resultset typed as xml, then i would expect (for the xml to be valid) that it has a root node regardless..... what concept am i missing if this is not the case?

|||I think the fact is, it isn't invalid XML, it is nothing. So if you return no data, then no XML is created.|||

i agree

BUT :)

that does mean that anything that uses the resultset requires a condition to check if it is Null and either 1)do nothing, or 2) subsitute it for what would be valid xml i.e. an empty parent node eg <Root />

i think a lot of applications would require number 2, and therefore think the XML functionality of sql2005 should have this built in.

|||

And I don't disagree with you, though you can use the cast and concatenation thing I posted earlier as a workaround.

If noone posts that I was wrong, consider posting your suggestion here: https://connect.microsoft.com/SQLServer/Feedback and then post in this thread that you have, and I will vote for it.

|||

how about this:-

CREATE PROCEDURE [dbo].[up_DoStuff]
(
-- params
)
AS
SET NOCOUNT ON;

DECLARE @.pXML XML
SET @.pXML = (
SELECT
...
FROM
...
WHERE
...
FOR
XML Path('Test'),
ELEMENTS,
ROOT('Tests'),
TYPE
)

SELECT ISNULL(@.pXML, '<Tests/>')

FOR XML PATH

Hi,
I have the following query:
SELECT
C.CompanyName,
O.OrderDate,
E.LastName AS 'Registered By'
FROM Customers C
JOIN Orders O
ON C.CustomerID=O.CustomerID
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
ORDER BY C.CompanyName
I need all information of orders for particular company, appear under the
element of that company but the query that I have written returns the name
of company again and again for each order:
SELECT
C.CompanyName AS "@.Company",
O.OrderDate AS "Order/OrderDate",
E.LastName AS "Order/RegisteredBy"
FROM Customers C
JOIN Orders O
ON C.CustomerID=O.CustomerID
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
ORDER BY C.CompanyName
FOR XML PATH
Any help would be greatly appreciated.
Leilaoops! seems that the problem solved:
SELECT
C.CompanyName AS "@.Company",
(SELECT
O.OrderDate AS 'Order/OrderDate',
E.LastName AS 'Order/RegisteredBy'
FROM Orders O
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
WHERE O.CustomerID=C.CUstomerID
FOR XML PATH(''),type)
FROM Customers C
ORDER BY C.CompanyName
FOR XML PATH('Customers'),ROOT('ROOT')
Thanks to BOL documentation team :-)
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23AjU8w2IGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following query:
> SELECT
> C.CompanyName,
> O.OrderDate,
> E.LastName AS 'Registered By'
> FROM Customers C
> JOIN Orders O
> ON C.CustomerID=O.CustomerID
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> ORDER BY C.CompanyName
>
> I need all information of orders for particular company, appear under the
> element of that company but the query that I have written returns the name
> of company again and again for each order:
> SELECT
> C.CompanyName AS "@.Company",
> O.OrderDate AS "Order/OrderDate",
> E.LastName AS "Order/RegisteredBy"
> FROM Customers C
> JOIN Orders O
> ON C.CustomerID=O.CustomerID
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> ORDER BY C.CompanyName
> FOR XML PATH
> Any help would be greatly appreciated.
> Leila
>
>|||Good documentation can help ;)
You can make your query slightly easier to maintain by writing the subquery
as
(SELECT
O.OrderDate AS 'OrderDate',
E.LastName AS 'RegisteredBy'
FROM Orders O
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
WHERE O.CustomerID=C.CUstomerID
FOR XML PATH('Order'),type)
Best regards
Michael
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23LnD5B3IGHA.3696@.TK2MSFTNGP15.phx.gbl...
> oops! seems that the problem solved:
> SELECT
> C.CompanyName AS "@.Company",
> (SELECT
> O.OrderDate AS 'Order/OrderDate',
> E.LastName AS 'Order/RegisteredBy'
> FROM Orders O
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> WHERE O.CustomerID=C.CUstomerID
> FOR XML PATH(''),type)
> FROM Customers C
> ORDER BY C.CompanyName
> FOR XML PATH('Customers'),ROOT('ROOT')
> Thanks to BOL documentation team :-)
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23AjU8w2IGHA.1676@.TK2MSFTNGP09.phx.gbl...
>