Sahil
Thu Jun 02 01:58:11 CDT 2005
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/
"Eugene Kogan [MSFT]" <ekogan@online.microsoft.com> wrote in message
news:%23SZ3CJxZFHA.2420@TK2MSFTNGP12.phx.gbl...
> 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.
>
> "Sahil Malik [MVP]" <contactmethrumyblog@nospam.com> wrote in message
> news:ulkwFswZFHA.612@TK2MSFTNGP12.phx.gbl...
>> 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/
>>
>>
>> "Eugene Kogan [MSFT]" <ekogan@online.microsoft.com> wrote in message
>> news:OQsg$0tZFHA.2520@TK2MSFTNGP09.phx.gbl...
>>> Forwarding to the other groups the original posting was sent to.
>>>
>>> "Eugene Kogan [MSFT]" <ekogan@online.microsoft.com> wrote in message
>>> news:OCFW0hlZFHA.3096@TK2MSFTNGP15.phx.gbl...
>>>> 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.
>>>>
>>>> "Sahil Malik [MVP]" <contactmethrumyblog@nospam.com> wrote in message
>>>> news:uRskRhPZFHA.2288@TK2MSFTNGP14.phx.gbl...
>>>>> 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/
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>