FOR XML EXPLICIT can't get element 4 going

[Pacific Fox]

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.

[Erland Sommarskog]

Pacific Fox (ta*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[=?ISO-8859-1?Q?clickfind(tm)?=]

Hi, thanks for replying.

Unfortunately it creates the right structure, except for it not
creating the name="" attribute. It creates the following (only showing
Annotation elements)

<Annotation about="www.10000steps.org.au" score="1">
<Label>_cse_ad-o6lgdody</Label>
</Annotation>
<Annotation about="www.101fm.asn.au" score="1">
<Label>_cse_ad-o6lgdody</Label>
</Annotation>
<Annotation about="www.121money.com.au" score="1">
<Label>_cse_ad-o6lgdody</Label>
</Annotation>
<Annotation about="www.1300rubbish.com.au" score="1">
<Label>_cse_ad-o6lgdody</Label>
</Annotation>

While it should be

<Annotation about="www.10000steps.org.au" score="1">
<Label name="_cse_ad-o6lgdody"/>
</Annotation>

Thanks in advance.