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
It is currently creating
I cannot get my head around how I can get the label
name="_cse_ad-o6lgdody" />
in each element. Does anyone know?
Thanks in advance.
Re: FOR XML EXPLICIT can"t get element 4 going
am 07.01.2008 23:36:35 von Erland Sommarskog
Pacific Fox (tacofleur@gmail.com) writes:
> The XML it needs to create is as following
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> It is currently creating
>
>
>
>
>
>
>
>
>
>
>
> I cannot get my head around how I can get the 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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: FOR XML EXPLICIT can"t get element 4 going
am 08.01.2008 02:10:57 von taco.fleur
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)
While it should be
Thanks in advance.
Re: FOR XML EXPLICIT can"t get element 4 going
am 08.01.2008 23:44:37 von Erland Sommarskog
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)
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> While it should be
>
>
>
>
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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: FOR XML EXPLICIT can"t get element 4 going
am 09.01.2008 02:39:37 von Pacific Fox
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 help with in return?