May 19, 2008 at 2:12 pm
Hello,
I am using FOR XML PATH to retrieve data from a set of tables as xml output. However, I run into an issue while trying to generate a CDATA section in the output xml.
The ' ' with '>'.
Here's my query:
/****************************************************************/
select a.MsgVerb as 'MESSAGE/@VERB',a.MsgType as 'MESSAGE/@TYPE',
isnull(a.SOURCE,'') as 'MESSAGE/@SRC',a.MsgVersion as 'MESSAGE/@VERSION',
a.MsgID as 'MESSAGE/MSGID',
convert(varchar,a.CaptureDatetimeUTC,126)+'Z' as 'MESSAGE/CAPTUREDATETIMEUTC',
convert(varchar,a.MsgDatetimeUTC,126)+'Z' as 'MESSAGE/DATETIMEUTC',
a.ParticipantCount as 'MESSAGE/PARTICIPANTS/@COUNT',
--Participant related fields
(select b.PartType as 'USER/@TYPE',
b.IsComplianceEnabled as 'USER/ISCOMPLIANCEENABLED',
b.UserAgent as 'USER/USERAGENT',
b.Device as 'USER/DEVICENAME',
b.URI as 'USER/URI',
b.FriendlyName as 'USER/FRIENDLYNAME',
b.Email as 'USER/EMAIL',
isnull(a.AuthenticationInfo,'') as 'USER/AUTHENTICATIONINFO',
isnull(a.ProxyAuthorization,'') as 'USER/PROXYAUTHORIZATION',
isnull(a.Routes,'') as 'USER/ROUTES',
isnull(a.RecordRoutes,'') as 'USER/RECORDROUTES',
isnull(a.Vias,'') as 'USER/VIAS'
from #Part b where a.MsgID=b.MsgID
order by b.PartType
for xml path(''),type
) as 'MESSAGE/PARTICIPANTS',
(select N' '
from #MsgRaw c where a.MsgID=c.MsgID
for xml path(''),type
) as 'MESSAGE/MSGRAW'
from #Msg a
for xml path(''),root('MESSAGES')
/****************************************************************/
Please let me know if anybody has encountered a similar problem. Is there an easy way to resolve this?
Thanks.
May 21, 2008 at 2:52 am
Why "for xml path('')"?
This tells the database engine to concatenate the values into a string, and the extra space is a side effect. When using the path option to build a well-formed XML you should always specify a node name.
E.g. (untested):
...
(select b.PartType as [@TYPE],
b.IsComplianceEnabled as [ISCOMPLIANCEENABLED],
b.UserAgent as Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com),
b.Device as [DEVICENAME],
b.URI as [URI],
b.FriendlyName as [FRIENDLYNAME],
b.Email as ,
isnull(a.AuthenticationInfo,'') as [AUTHENTICATIONINFO],
isnull(a.ProxyAuthorization,'') as [PROXYAUTHORIZATION],
isnull(a.Routes,'') as [ROUTES],
isnull(a.RecordRoutes,'') as [RECORDROUTES],
isnull(a.Vias,'') as [VIAS]
from #Part b where a.MsgID=b.MsgID
order by b.PartType
for xml path('USER'),type
) as [MESSAGE/PARTICIPANTS],
...
Also, avoid using single quotes to enclose column names. Use the standard method with square brackets as above.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
July 8, 2008 at 1:33 am
To generate a CDATA section, you should use FOR XML EXPLICIT.
.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply