Posted by Pacific Fox on 01/07/08 08:55

Hello all,

I'm trying to generate some XML directly from MS SQL with the
following code

SELECT 1 AS tag
, NULL AS parent
, NULL AS [GoogleCustomizations!1]
, NULL AS [Annotations!2]
, NULL AS [Annotation!3]
, NULL AS [Annotation!3!about]
, NULL AS [Annotation!3!score]
, NULL AS [Label!4]
, NULL AS [Label!4!name]
UNION
SELECT 2 AS tag
, 1 AS parent
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
UNION
SELECT TOP 50 3 AS tag
, 2 AS parent
, NULL
, NULL
, NULL
, 'www.' + domainName
, 1 -- score
, NULL
, NULL
FROM tbl_auDomainName
UNION
SELECT 4 AS tag
, 3 AS parent
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, '_cse_ad-o6lgdody'
FOR XML EXPLICIT

The XML it needs to create is as following

<GoogleCustomizations>
<Annotations>
<Annotation about="www.clickfind.com.au/*" score="1">
<Label name="_cse_ad-o6lgdody" />
</Annotation>
<Annotation about="www.lookle.com/*" score="1">
<Label name="_cse_ad-o6lgdody" />
</Annotation>
<Annotation about="www.sensis.com.au/*" score="1">
<Label name="_cse_ad-o6lgdody" />
</Annotation>
</Annotations>
</GoogleCustomizations>

It is currently creating

<GoogleCustomizations>
<Annotations>
<Annotation about="www.10000steps.org.au" score="1" />
<Annotation about="www.101fm.asn.au" score="1" />
<Annotation about="www.aao.gov.au" score="1">
<Label name="_cse_ad-o6lgdody" />
</Annotation>
</Annotations>
</GoogleCustomizations>

I cannot get my head around how I can get the label <Label
name="_cse_ad-o6lgdody" />
in each element. Does anyone know?

Thanks in advance.

• Next in forum: ? about users
• Prev in forum: Re: Error handling in stored procedure AND checking
• Thread view: FOR XML EXPLICIT can't get element 4 going

[Reply to this message]