Posted by Erland Sommarskog on 01/07/08 22:36

Pacific Fox (tacofleur@gmail.com) writes:
> 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?

This seems to do what you want:

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 [Annotation!3!Label!element]
UNION
SELECT 2 AS tag
, 1 AS parent
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
UNION
SELECT 3 AS tag
, 2 AS parent
, NULL
, NULL
, EmployeeID
, FirstName
, 1 -- score
, '_cse_ad-o6lgdody'
FROM Employees
FOR XML EXPLICIT



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Select Max values from queries for multiple Schedule_Number
• Prev in forum: Re: ? about users
• Thread view: Re: FOR XML EXPLICIT can't get element 4 going

[Reply to this message]