Thank you for your feedback, Sahil. EXPLICIT mode of FOR XML is not
being deprecated.
Factors affecting FOR XML EXPLICIT and nested correlated sub-query FOR
XML performance are different and the conversion cost may not be
dominant. The best is to measure what works best in your particular
scenario, and then take into account other factors like code
maintainability.
With SQL Server 2005 CTP 15 available soon it will be the best time to
measure performance and send us feedback.
Note that XML from a varchar/text column will be converted to UTF-16
when serialized by FOR XML EXPLICIT code. In some cases the conversion
can be delayed to client side processing - when retrieving XML using
"xml" directive via SQL OLEDB client.
Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no
rights.
Thank you Michael. This solution is workable. However it has a overhead
of cast, but I guess thats allright. For extremely high demand
situations there is still FOR XML EXPLICIT - So I guess EXPLICIT isn't
getting deprecated anytime soon?
.
- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
Here is a more complete example that shows how to get !xml and
!xmltext behaviour:
create table t(i int, x nvarchar(50))
go
insert into t
select 1, N'<a b="1"><c>2</c></a>'
union
select 2, N'<a><d>3</d></a>'
go
select 1 as tag, NULL as parent, i as "r!1!i", x as "r!1!x!xml"
from t
for xml explicit
select i as "@i", CAST(x as XML) as "x"
from t
for xml path('r')
select 1 as tag, NULL as parent, i as "r!1!i", x as "r!1!x!xmltext"
from t
for xml explicit
select i as "@i", CAST(x as XML).query('<x>{*/@*, */node()}</x>')
from t
for xml path('r')
Your example is much simpler written as
select cast(monkeyname as XML) as "monkey" from ...
This does not generate more sizes since you are going to generate the
XML in either case (and the talk about indexing overhead is a red
herring for writing FOR XML queries).
Next one please
.
Best regards
Michael
Eugene,
Well one usecase was the query I presented with the anticipated
results. In a practical real world I guess I could just live with
doing the element concatenation in the data layer (wouldn't be so
bad), but ...
Okay here is a usecase that cannot be acheived without using
directives, and might arise much too often -
Say I have a column where I have stored XML as varchar (lets say it
is legacy), and I want that XML to simply appear as nodes within the
results of my FOR XML query. Now without directives, I cannot
acheive this as Sql2k/2k5 will entity encode the special characters
and completely mess up my XML. However with the xmltext directive I
could acheive this. One answer could be "Just change the data type
to XML", but lets say I can't do that for various reasons - on
reason can be XML columns and their indexes occupy much more space
than a regular varchar column might. (If I am not mistaken XML
datatype occupies apprx 3X the space of a varchar and the primary
index is 5X?)
Why can't I specify a directive like this -
Select monkeyname [animals/monkey!xmltext] from ...
?
Now one option would be to decode the entity encoding in the data
layer - but that would be hyper inefficient if I had to do it for
every row selected - i.e. not a good solution.
Another option could be to use nested queries - again thats not the
best idea.
- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
With PATH mode of FOR XML (as well as RAW and AUTO modes), together
with nesting FOR XML queries using correlated sub-query syntax, you
can do most of what you could do with FOR XML EXPLICIT - in a
simpler and more manageable way.
Only the functionality of "cdata", "hide", and "xmltext" directives
of FOR XML EXPLICIT is not matched by the PATH mode. Please comment
if you believe it is important for your use cases.
FOR XML in SQL Server 2005 is described in BOL as well as in
multiple other online publications:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp
is a good start.
Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no
rights.
message Thank you Eugene, this is fantastic. I got a similar response from
yet another NG (wink wink), so I guess it might be accurate to say
that FOR XML PATH is not a replacement for EXPLICIT - just a
convenient way to do certain things. (For instance, I guess you
can't do XML Directives in FOR XML PATH).
- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
message Forwarding to the other groups the original posting was sent to.
message Sahil,
PATH mode of FOR XML will not help you add one-to-many
properties from a joined table to the XML formatting of your
rowset.
You'll need to use FOR XML in the sub-query syntax. You can also
use AUTO mode for such a simple XML shape. Here are examples of
PATH, ROW, and AUTO:
--FOR XML PATH
SELECT G.GrandParentID AS [@GrandParentID],
(SELECT LTRIM(RTRIM(S.SonName)) AS [@SonName]
FROM Son S WHERE G.GrandParentID = S.GrandParentID
ORDER BY [@SonName]
FOR XML PATH('Son'),TYPE)
FROM GrandParent G
WHERE G.GrandParentID IN (SELECT GrandParentID FROM Son)
ORDER BY G.GrandParentID
FOR XML PATH('GrandParent'), ROOT('GrandParents')
-- FOR XML RAW
SELECT G.GrandParentID,
(SELECT LTRIM(RTRIM(S.SonName)) AS SonName
FROM Son S WHERE G.GrandParentID = S.GrandParentID
ORDER BY SonName
FOR XML RAW('Son'),TYPE)
FROM GrandParent G
WHERE G.GrandParentID IN (SELECT GrandParentID FROM Son)
ORDER BY G.GrandParentID
FOR XML RAW('GrandParent'), ROOT('GrandParents')
-- FOR XML AUTO
SELECT GrandParent.GrandParentID, Son.SonName
FROM
(SELECT G.GrandParentID
FROM GrandParent G
WHERE G.GrandParentID IN
(SELECT GrandParentID FROM Son)
) GrandParent
JOIN
(SELECT GrandParentID, LTRIM(RTRIM(SonName)) AS SonName
FROM Son) Son
ON GrandParent.GrandParentID = Son.GrandParentID
ORDER BY GrandParent.GrandParentID, Son.SonName
FOR XML AUTO, ROOT('GrandParents')
Regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers
no rights.
message Okay, this is a really simple question.
This for xml explicit query ---
SELECT 1 as Tag,
NULL as Parent,
G.GrandParentID as [GrandParent!1!GrandParentID],
NULL as [Son!2!SonName]
FROM GrandParent G
WHERE G.GrandParentID IN (Select GrandParentID from Son)
UNION ALL
SELECT 2 as Tag,
1 as Parent,
S.GrandParentID,
LTRIM(RTRIM(S.SonName))
FROM GrandParent G, Son S
WHERE G.GrandParentID = S.GrandParentID
ORDER BY [GrandParent!1!GrandParentID], [Son!2!SonName]
FOR XML EXPLICIT , ROOT('XML')
Produces ---
<XML>
<GrandParent GrandParentID="1">
<Son SonName="Han" />
</GrandParent>
<GrandParent GrandParentID="2">
<Son SonName="Darth" />
<Son SonName="Luke" />
</GrandParent>
</XML>
But when I try writing this same query as FOR XML PATH as -
Select
G.GrandParentID GrandParent/@GrandParentID,
RTRIM(S.SonName) GrandParent/Son/@SonName
FROM
GrandParent G INNER JOIN SON S ON G.GrandParentID =
S.GrandParentID
For Xml Path('GrandParent'), Root('XML')
- I get the following
<XML>
<GrandParent>
<GrandParent GrandParentID="2">
<Son SonName="Luke" />
</GrandParent>
</GrandParent>
<GrandParent>
<GrandParent GrandParentID="2">
<Son SonName="Darth" />
</GrandParent>
</GrandParent>
<GrandParent>
<GrandParent GrandParentID="1">
<Son SonName="Han" />
</GrandParent>
</GrandParent>
</XML>
How the heck do I tell SQL Server 2005 that all sons (Luke &
Darth) with GrandParentID=2 are supposed to be nested into one
element???
Thanks for ur help !!!
- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
