|
|
Posted by Erland Sommarskog on 01/08/08 22:44
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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|