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.

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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.

clickfind(tm) (taco.fleur@clickfind.com.au) writes:
> 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>

This was about driving me nuts, but I don't do FOR XML that often. I also
looked at XML PATH in SQL 2005, which they say is easier to use than
EXPLCIT. And indeed, it's dead simple:

SELECT EmployeeID AS [Annotation/@score],
FirstName AS [Annotation/@about],
'_cse_ad-o6lgdody' AS [Annotation/Label/@name]
FROM Employees
FOR XML PATH(''), ROOT('Annotations')

Almost. You may not that your outermost tag is missing. I was not able
to figure out how to have a two-level root, but I think it may be possible
by nesting FOR XML queries.

Anyway, I was able to solve the problem with XML EXPLICIT. It does
pay off to read the manual:

In constructing the XML, the rows in the universal table are processed
in order. Therefore, to retrieve the correct children instances
associated with their parent, the rows in the rowset must be ordered so
that each parent node is immediately followed by its children.

With that in mind, here the query with the Employees table as a stand-in
for your table:

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







--
Erland Sommarskog, SQL Server MVP, esquel@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

You're a champ!

I got it to work with your code, with one slight modification, see
final code below.

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]
, NULL AS [Label!4!Name]
UNION ALL
SELECT 2 AS tag
, 1 AS parent
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
UNION ALL
SELECT 3 AS tag
, 2 AS parent
, NULL
, NULL
, NULL
, domainName
, 1 -- score
, NULL
, NULL
FROM domainName
UNION ALL
SELECT 4 AS tag
, 3 AS parent
, NULL
, NULL
, NULL
, domainName
, 1 -- score
, NULL
, '_cse_ad-o6lgdody'
FROM domainName
ORDER BY [Annotation!3!about], tag
FOR XML EXPLICIT

Thanks a million! Anything I can with in return?