Me reading the docs and applying the syntax to my query. WTF, why doesn't this work?
30 minutes later, having picked the query apart. FINE, let me get on a server with AdventureWorks and run the query from BoL.
The documentation is wrong (or SQL Server behaviour changed between 2012 and now w/r/t subqueries with FOR XML PATH)
Specifically, "Generate a list using PATH mode" the second subquery for ProductNames results in XML safe instead of a merged result as shown https://learn.microsoft.com/en-us/sql/relational-databases/xml/examples-using-path-mode?view=sql-server-ver16#generate-a-value-list-using-path-mode
Bill Fellows /
npub1c9…rp72p
2023-08-07 18:55:15
Author Public Key
npub1c9shmy8un2d3l0up6c9wcz6p2kkzw8v2u5mml5qpklqxhtpjturqzrp72pPublished at
2023-08-07 18:55:15Event JSON
{
"id": "e68a27ea92723d207542e1dbe3756b95228bc92ecb9459c08c53c795510225d1",
"pubkey": "c1617d90fc9a9b1fbf81d60aec0b4155ac271d8ae537bfd001b7c06bac325f06",
"created_at": 1691434515,
"kind": 1,
"tags": [
[
"proxy",
"https://dataplatform.social/users/billinkc/statuses/110849852436950471",
"activitypub"
]
],
"content": "Me reading the docs and applying the syntax to my query. WTF, why doesn't this work?\n\n30 minutes later, having picked the query apart. FINE, let me get on a server with AdventureWorks and run the query from BoL. \n\nThe documentation is wrong (or SQL Server behaviour changed between 2012 and now w/r/t subqueries with FOR XML PATH)\n\nSpecifically, \"Generate a list using PATH mode\" the second subquery for ProductNames results in XML safe instead of a merged result as shown https://learn.microsoft.com/en-us/sql/relational-databases/xml/examples-using-path-mode?view=sql-server-ver16#generate-a-value-list-using-path-mode",
"sig": "630f64f592e3f40d77e5bcc974c569c0a4afb9a7d0f091a05b606238dda1da044624e3a6b561d887f965a9f027e7adf1bfd49e7afd0430148beb41f360a2b243"
}