Yukon Question: FOR XML EXPLICIT Vs. FOR XML PATH

Sahil,
Have a look at the keywords "dataset nested" or/and "relation nested" on
msdn.
I have answered sometimes on this question, however I am not sure anymore
which newsgroup. It is not very nice documentated.
Cor
Cor -
There is no mention of "dataset" in my question.
I looked for that on MSDN and google before I posted the question here.
- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
"Cor Ligthert" <notmyfirstname@.planet.nl> wrote in message
news:uJ0GDfQZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Sahil,
> Have a look at the keywords "dataset nested" or/and "relation nested" on
> msdn.
> I have answered sometimes on this question, however I am not sure anymore
> which newsgroup. It is not very nice documentated.
> Cor
>
Sahil,
> There is no mention of "dataset" in my question.
> I looked for that on MSDN and google before I posted the question here.
>
Than use a dataset with a relation and nested.
Or is it more meant as an academical question?
Cor
> Or is it more meant as an academical question?
Yes !!! I am trying to figure out a way to make this work.
- SM
"Cor Ligthert" <notmyfirstname@.planet.nl> wrote in message
news:uXi4OnoZFHA.1148@.tk2msftngp13.phx.gbl...
> Sahil,
> >
> > There is no mention of "dataset" in my question.
> > I looked for that on MSDN and google before I posted the question here.
> >
> Than use a dataset with a relation and nested.
> Or is it more meant as an academical question?
> Cor
>
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/
>>
>>
>
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/
>>
>>
>>
>
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/
>>
>>
>>
>>
>
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/
>>
>>
>>
>>
>>
>
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
"Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
news:emxZxB0ZFHA.228@.TK2MSFTNGP12.phx.gbl...
> 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/
>>
>>
>>
>>
>>
>>
>
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')
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23NlH$o7ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
> 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
> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
> news:emxZxB0ZFHA.228@.TK2MSFTNGP12.phx.gbl...
>> 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/
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
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/
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:upILOK8ZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> 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')
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23NlH$o7ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
>> 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
>> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
>> news:emxZxB0ZFHA.228@.TK2MSFTNGP12.phx.gbl...
>> 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/
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
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.
"Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
news:%238XPeqTaFHA.2076@.TK2MSFTNGP15.phx.gbl...
> 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/
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:upILOK8ZFHA.1404@.TK2MSFTNGP09.phx.gbl...
>> 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')
>>
>> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
>> news:%23NlH$o7ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
>> 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
>> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
>> news:emxZxB0ZFHA.228@.TK2MSFTNGP12.phx.gbl...
>> 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/
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
I'm missing something major here.
>>Note that XML from a varchar/text column will be converted to UTF-16 when
>>serialized by FOR XML EXPLICIT code
For XML PATH is not UTF-16?
- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
"Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
news:e8G6dsiaFHA.616@.TK2MSFTNGP12.phx.gbl...
> 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.
>
> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
> news:%238XPeqTaFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> 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/
>>
>> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
>> news:upILOK8ZFHA.1404@.TK2MSFTNGP09.phx.gbl...
>> 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')
>>
>> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
>> news:%23NlH$o7ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
>> 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
>> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
>> news:emxZxB0ZFHA.228@.TK2MSFTNGP12.phx.gbl...
>> 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/
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
I was referring to your use case of storing XML in varchar/text column you
mentioned below. This is a valid use case if disk space and performance of
XML retrieval as a whole is the priority. However, in this case you can't
completely avoid any data conversion cost if XML instances need to be
formatted as XML together with other data - there will be at least
conversion from varchar/text to nvarchar/ntext somewhere along the way. The
note in my email below was a hint on how to offload the conversion from the
server to client in some cases.
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:%23BMbuUjaFHA.3040@.TK2MSFTNGP14.phx.gbl...
> I'm missing something major here.
>>Note that XML from a varchar/text column will be converted to UTF-16 when
>>serialized by FOR XML EXPLICIT code
> For XML PATH is not UTF-16?
> - Sahil Malik [MVP]
> http://codebetter.com/blogs/sahil.malik/
>
>
> "Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
> news:e8G6dsiaFHA.616@.TK2MSFTNGP12.phx.gbl...
>> 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.
>>
>> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
>> news:%238XPeqTaFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> 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/
>>
>> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
>> news:upILOK8ZFHA.1404@.TK2MSFTNGP09.phx.gbl...
>> 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')
>>
>> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
>> news:%23NlH$o7ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
>> 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
>> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
>> news:emxZxB0ZFHA.228@.TK2MSFTNGP12.phx.gbl...
>> 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/
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
Ok got it .. !!! :) Thanks !!
- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
"Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
news:uw1iWslaFHA.3840@.tk2msftngp13.phx.gbl...
>I was referring to your use case of storing XML in varchar/text column you
>mentioned below. This is a valid use case if disk space and performance of
>XML retrieval as a whole is the priority. However, in this case you can't
>completely avoid any data conversion cost if XML instances need to be
>formatted as XML together with other data - there will be at least
>conversion from varchar/text to nvarchar/ntext somewhere along the way. The
>note in my email below was a hint on how to offload the conversion from the
>server to client in some cases.
> 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:%23BMbuUjaFHA.3040@.TK2MSFTNGP14.phx.gbl...
>> I'm missing something major here.
>>Note that XML from a varchar/text column will be converted to UTF-16
>>when serialized by FOR XML EXPLICIT code
>> For XML PATH is not UTF-16?
>> - Sahil Malik [MVP]
>> http://codebetter.com/blogs/sahil.malik/
>>
>>
>> "Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
>> news:e8G6dsiaFHA.616@.TK2MSFTNGP12.phx.gbl...
>> 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.
>>
>> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
>> news:%238XPeqTaFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> 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/
>>
>> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
>> news:upILOK8ZFHA.1404@.TK2MSFTNGP09.phx.gbl...
>> 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')
>>
>> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
>> news:%23NlH$o7ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
>> 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
>> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
>> news:emxZxB0ZFHA.228@.TK2MSFTNGP12.phx.gbl...
>>> 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/
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
The EXPLICIT mode does not get deprecated anytime soon. We offer features
for all livestyles :-).
Note that the EXPLICIT mode solution also would perform a conversion into
XML. Whether you make the cost explicit in the syntax with the CAST
expression or not is irrelevant.
There are places where EXPLICIT mode will continue to be faster. But given
the large complexity overhead, I strongly recommend it only as the option of
last resort. Functionality-wise, there are really only marginal cases
speaking in favor of it...
Best regards
Michael
"Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
news:%238XPeqTaFHA.2076@.TK2MSFTNGP15.phx.gbl...
> 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/
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:upILOK8ZFHA.1404@.TK2MSFTNGP09.phx.gbl...
>> 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')
>>
>> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
>> news:%23NlH$o7ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
>> 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
>> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
>> news:emxZxB0ZFHA.228@.TK2MSFTNGP12.phx.gbl...
>> 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/
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
Fantastic Michael. I think I get the full picture now. Thank you for your
excellent help.
- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23EtG2f%23aFHA.3328@.TK2MSFTNGP09.phx.gbl...
> The EXPLICIT mode does not get deprecated anytime soon. We offer features
> for all livestyles :-).
> Note that the EXPLICIT mode solution also would perform a conversion into
> XML. Whether you make the cost explicit in the syntax with the CAST
> expression or not is irrelevant.
> There are places where EXPLICIT mode will continue to be faster. But given
> the large complexity overhead, I strongly recommend it only as the option
> of last resort. Functionality-wise, there are really only marginal cases
> speaking in favor of it...
> Best regards
> Michael
> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
> news:%238XPeqTaFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> 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/
>>
>> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
>> news:upILOK8ZFHA.1404@.TK2MSFTNGP09.phx.gbl...
>> 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')
>>
>> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
>> news:%23NlH$o7ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
>> 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
>> "Sahil Malik [MVP]" <contactmethrumyblog@.nospam.com> wrote in message
>> news:emxZxB0ZFHA.228@.TK2MSFTNGP12.phx.gbl...
>> 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/
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>