|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
FOR XML: How to prevent nesting elementshow... Contact1 and COntact2 belong to the same Customer1. I would like the element customer duplicated as below. How should I do that? Note; Currently, I get always 2 contact under the same Customer elements. SELECT Customer.ID, Contact.ContactID FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID FOR XML AUTO, ELEMENTS <Customer> <CustomerID>Customer1</CustomerID> <Contact> <ContactID>Contact 1</ContactID> </Contact> </Customer> <Customer> <CustomerID>Customer1</CustomerID> <Contact> <ContactID>Contact 2</ContactID> </Contact> </Customer> Thank you! David Parenteau How about (for SQL Server 2005 and later)
SELECT Customer.CustomerID AS [CustomerID], Contact.ContractID AS [ContactID] FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID FOR XML PATH('Customer') Show quoteHide quote "David Parenteau" <DavidParent***@discussions.microsoft.com> wrote in message news:FC3889EB-5C74-4CEB-AD4C-8F9678FC715D@microsoft.com... > Hi! I would like the following result returned, but I'm not able to find > out > how... > Contact1 and COntact2 belong to the same Customer1. I would like the > element > customer duplicated as below. How should I do that? > > Note; Currently, I get always 2 contact under the same Customer elements. > > SELECT Customer.ID, Contact.ContactID > FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID > FOR XML AUTO, ELEMENTS > > > <Customer> > <CustomerID>Customer1</CustomerID> > <Contact> > <ContactID>Contact 1</ContactID> > </Contact> > </Customer> > <Customer> > <CustomerID>Customer1</CustomerID> > <Contact> > <ContactID>Contact 2</ContactID> > </Contact> > </Customer> > > Thank you! > > David Parenteau Sorry, I forgot to specify that it should be under SQL 2000, unfortunately.
Show quoteHide quote "Bob Beauchemin" wrote: > How about (for SQL Server 2005 and later) > > SELECT Customer.CustomerID AS [CustomerID], > Contact.ContractID AS [ContactID] > FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID > FOR XML PATH('Customer') > > "David Parenteau" <DavidParent***@discussions.microsoft.com> wrote in > message news:FC3889EB-5C74-4CEB-AD4C-8F9678FC715D@microsoft.com... > > Hi! I would like the following result returned, but I'm not able to find > > out > > how... > > Contact1 and COntact2 belong to the same Customer1. I would like the > > element > > customer duplicated as below. How should I do that? > > > > Note; Currently, I get always 2 contact under the same Customer elements. > > > > SELECT Customer.ID, Contact.ContactID > > FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID > > FOR XML AUTO, ELEMENTS > > > > > > <Customer> > > <CustomerID>Customer1</CustomerID> > > <Contact> > > <ContactID>Contact 1</ContactID> > > </Contact> > > </Customer> > > <Customer> > > <CustomerID>Customer1</CustomerID> > > <Contact> > > <ContactID>Contact 2</ContactID> > > </Contact> > > </Customer> > > > > Thank you! > > > > David Parenteau > > I got this to work for SQL 2000 using FOR XML EXPLICIT:
SELECT 1 AS Tag, NULL AS Parent, NULL AS [Customer!1], cu.ID AS [CustomerID!2], NULL AS [Contact!3], NULL AS [ContactID!4], co.ID AS [ID!5!hide] FROM Customer cu INNER JOIN Contact co ON cu.CustomerID = co.CustomerID UNION ALL SELECT 2 AS Tag, 1 AS Parent, NULL, cu.ID, NULL, ContactID, co.ID AS [ID!5!hide] FROM Customer cu INNER JOIN Contact co ON cu.CustomerID = co.CustomerID UNION ALL SELECT 3 AS Tag, 1 AS Parent, NULL, cu.ID, NULL, ContactID, co.ID AS [ID!5!hide] FROM Customer cu INNER JOIN Contact co ON cu.CustomerID = co.CustomerID UNION ALL SELECT 4 AS Tag, 3 AS Parent, NULL, cu.ID, NULL, ContactID, co.ID AS [ID!5!hide] FROM Customer cu INNER JOIN Contact co ON cu.CustomerID = co.CustomerID ORDER BY [ID!5!hide], [CustomerID!2] FOR XML EXPLICIT Show quoteHide quote "David Parenteau" wrote: > Hi! I would like the following result returned, but I'm not able to find out > how... > Contact1 and COntact2 belong to the same Customer1. I would like the element > customer duplicated as below. How should I do that? > > Note; Currently, I get always 2 contact under the same Customer elements. > > SELECT Customer.ID, Contact.ContactID > FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID > FOR XML AUTO, ELEMENTS > > > <Customer> > <CustomerID>Customer1</CustomerID> > <Contact> > <ContactID>Contact 1</ContactID> > </Contact> > </Customer> > <Customer> > <CustomerID>Customer1</CustomerID> > <Contact> > <ContactID>Contact 2</ContactID> > </Contact> > </Customer> > > Thank you! > > David Parenteau Hi Bob... I think I see, but if a customer has up to 10 contacts, should I
put up to 10 UNION ALL? Can the tag be the COntactID in order to get "different" tag number but with only one UNION ALL? David Show quoteHide quote "Bob" wrote: > I got this to work for SQL 2000 using FOR XML EXPLICIT: > > SELECT > 1 AS Tag, > NULL AS Parent, > NULL AS [Customer!1], > cu.ID AS [CustomerID!2], > NULL AS [Contact!3], > NULL AS [ContactID!4], > co.ID AS [ID!5!hide] > > FROM Customer cu > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > UNION ALL > > SELECT > 2 AS Tag, > 1 AS Parent, > NULL, > cu.ID, > NULL, > ContactID, > co.ID AS [ID!5!hide] > FROM Customer cu > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > UNION ALL > > SELECT > 3 AS Tag, > 1 AS Parent, > NULL, > cu.ID, > NULL, > ContactID, > co.ID AS [ID!5!hide] > FROM Customer cu > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > UNION ALL > > SELECT > 4 AS Tag, > 3 AS Parent, > NULL, > cu.ID, > NULL, > ContactID, > co.ID AS [ID!5!hide] > FROM Customer cu > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > ORDER BY [ID!5!hide], [CustomerID!2] > > FOR XML EXPLICIT > > > "David Parenteau" wrote: > > > Hi! I would like the following result returned, but I'm not able to find out > > how... > > Contact1 and COntact2 belong to the same Customer1. I would like the element > > customer duplicated as below. How should I do that? > > > > Note; Currently, I get always 2 contact under the same Customer elements. > > > > SELECT Customer.ID, Contact.ContactID > > FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID > > FOR XML AUTO, ELEMENTS > > > > > > <Customer> > > <CustomerID>Customer1</CustomerID> > > <Contact> > > <ContactID>Contact 1</ContactID> > > </Contact> > > </Customer> > > <Customer> > > <CustomerID>Customer1</CustomerID> > > <Contact> > > <ContactID>Contact 2</ContactID> > > </Contact> > > </Customer> > > > > Thank you! > > > > David Parenteau No that should work. Try it!
In FOR XML EXPLICIT, extra sections of UNION ALL are for extra columns, or really elements and attributes in XML terms. This type of thing is much easier to do in SQL 2005 onwards with FOR XML PATH. HTH wBob Show quoteHide quote "David Parenteau" wrote: > Hi Bob... I think I see, but if a customer has up to 10 contacts, should I > put up to 10 UNION ALL? Can the tag be the COntactID in order to get > "different" tag number but with only one UNION ALL? > > David > > "Bob" wrote: > > > I got this to work for SQL 2000 using FOR XML EXPLICIT: > > > > SELECT > > 1 AS Tag, > > NULL AS Parent, > > NULL AS [Customer!1], > > cu.ID AS [CustomerID!2], > > NULL AS [Contact!3], > > NULL AS [ContactID!4], > > co.ID AS [ID!5!hide] > > > > FROM Customer cu > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > UNION ALL > > > > SELECT > > 2 AS Tag, > > 1 AS Parent, > > NULL, > > cu.ID, > > NULL, > > ContactID, > > co.ID AS [ID!5!hide] > > FROM Customer cu > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > UNION ALL > > > > SELECT > > 3 AS Tag, > > 1 AS Parent, > > NULL, > > cu.ID, > > NULL, > > ContactID, > > co.ID AS [ID!5!hide] > > FROM Customer cu > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > UNION ALL > > > > SELECT > > 4 AS Tag, > > 3 AS Parent, > > NULL, > > cu.ID, > > NULL, > > ContactID, > > co.ID AS [ID!5!hide] > > FROM Customer cu > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > ORDER BY [ID!5!hide], [CustomerID!2] > > > > FOR XML EXPLICIT > > > > > > "David Parenteau" wrote: > > > > > Hi! I would like the following result returned, but I'm not able to find out > > > how... > > > Contact1 and COntact2 belong to the same Customer1. I would like the element > > > customer duplicated as below. How should I do that? > > > > > > Note; Currently, I get always 2 contact under the same Customer elements. > > > > > > SELECT Customer.ID, Contact.ContactID > > > FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID > > > FOR XML AUTO, ELEMENTS > > > > > > > > > <Customer> > > > <CustomerID>Customer1</CustomerID> > > > <Contact> > > > <ContactID>Contact 1</ContactID> > > > </Contact> > > > </Customer> > > > <Customer> > > > <CustomerID>Customer1</CustomerID> > > > <Contact> > > > <ContactID>Contact 2</ContactID> > > > </Contact> > > > </Customer> > > > > > > Thank you! > > > > > > David Parenteau I got the the BOL and it appears very difficult to understand this! I
understand that I can do what I want (more complex than the example). I will pass time on this and let you know if I needmore help than the book online! Merci bob :) David Parenteau, Montreal. Show quoteHide quote "Bob" wrote: > No that should work. Try it! > > In FOR XML EXPLICIT, extra sections of UNION ALL are for extra columns, or > really elements and attributes in XML terms. > > This type of thing is much easier to do in SQL 2005 onwards with FOR XML PATH. > > HTH > wBob > > "David Parenteau" wrote: > > > Hi Bob... I think I see, but if a customer has up to 10 contacts, should I > > put up to 10 UNION ALL? Can the tag be the COntactID in order to get > > "different" tag number but with only one UNION ALL? > > > > David > > > > "Bob" wrote: > > > > > I got this to work for SQL 2000 using FOR XML EXPLICIT: > > > > > > SELECT > > > 1 AS Tag, > > > NULL AS Parent, > > > NULL AS [Customer!1], > > > cu.ID AS [CustomerID!2], > > > NULL AS [Contact!3], > > > NULL AS [ContactID!4], > > > co.ID AS [ID!5!hide] > > > > > > FROM Customer cu > > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > > > UNION ALL > > > > > > SELECT > > > 2 AS Tag, > > > 1 AS Parent, > > > NULL, > > > cu.ID, > > > NULL, > > > ContactID, > > > co.ID AS [ID!5!hide] > > > FROM Customer cu > > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > > > UNION ALL > > > > > > SELECT > > > 3 AS Tag, > > > 1 AS Parent, > > > NULL, > > > cu.ID, > > > NULL, > > > ContactID, > > > co.ID AS [ID!5!hide] > > > FROM Customer cu > > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > > > UNION ALL > > > > > > SELECT > > > 4 AS Tag, > > > 3 AS Parent, > > > NULL, > > > cu.ID, > > > NULL, > > > ContactID, > > > co.ID AS [ID!5!hide] > > > FROM Customer cu > > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > > > ORDER BY [ID!5!hide], [CustomerID!2] > > > > > > FOR XML EXPLICIT > > > > > > > > > "David Parenteau" wrote: > > > > > > > Hi! I would like the following result returned, but I'm not able to find out > > > > how... > > > > Contact1 and COntact2 belong to the same Customer1. I would like the element > > > > customer duplicated as below. How should I do that? > > > > > > > > Note; Currently, I get always 2 contact under the same Customer elements. > > > > > > > > SELECT Customer.ID, Contact.ContactID > > > > FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID > > > > FOR XML AUTO, ELEMENTS > > > > > > > > > > > > <Customer> > > > > <CustomerID>Customer1</CustomerID> > > > > <Contact> > > > > <ContactID>Contact 1</ContactID> > > > > </Contact> > > > > </Customer> > > > > <Customer> > > > > <CustomerID>Customer1</CustomerID> > > > > <Contact> > > > > <ContactID>Contact 2</ContactID> > > > > </Contact> > > > > </Customer> > > > > > > > > Thank you! > > > > > > > > David Parenteau Post over on the MSDN newsgroup which is busier than this one and someone is
more likely to pick it up: http://social.msdn.microsoft.com/forums/en-US/sqlxml/threads/ Show quoteHide quote "David Parenteau" wrote: > I got the the BOL and it appears very difficult to understand this! I > understand that I can do what I want (more complex than the example). I will > pass time on this and let you know if I needmore help than the book online! > > Merci bob :) > > David Parenteau, Montreal. > > "Bob" wrote: > > > No that should work. Try it! > > > > In FOR XML EXPLICIT, extra sections of UNION ALL are for extra columns, or > > really elements and attributes in XML terms. > > > > This type of thing is much easier to do in SQL 2005 onwards with FOR XML PATH. > > > > HTH > > wBob > > > > "David Parenteau" wrote: > > > > > Hi Bob... I think I see, but if a customer has up to 10 contacts, should I > > > put up to 10 UNION ALL? Can the tag be the COntactID in order to get > > > "different" tag number but with only one UNION ALL? > > > > > > David > > > > > > "Bob" wrote: > > > > > > > I got this to work for SQL 2000 using FOR XML EXPLICIT: > > > > > > > > SELECT > > > > 1 AS Tag, > > > > NULL AS Parent, > > > > NULL AS [Customer!1], > > > > cu.ID AS [CustomerID!2], > > > > NULL AS [Contact!3], > > > > NULL AS [ContactID!4], > > > > co.ID AS [ID!5!hide] > > > > > > > > FROM Customer cu > > > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > > > > > UNION ALL > > > > > > > > SELECT > > > > 2 AS Tag, > > > > 1 AS Parent, > > > > NULL, > > > > cu.ID, > > > > NULL, > > > > ContactID, > > > > co.ID AS [ID!5!hide] > > > > FROM Customer cu > > > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > > > > > UNION ALL > > > > > > > > SELECT > > > > 3 AS Tag, > > > > 1 AS Parent, > > > > NULL, > > > > cu.ID, > > > > NULL, > > > > ContactID, > > > > co.ID AS [ID!5!hide] > > > > FROM Customer cu > > > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > > > > > UNION ALL > > > > > > > > SELECT > > > > 4 AS Tag, > > > > 3 AS Parent, > > > > NULL, > > > > cu.ID, > > > > NULL, > > > > ContactID, > > > > co.ID AS [ID!5!hide] > > > > FROM Customer cu > > > > INNER JOIN Contact co ON cu.CustomerID = co.CustomerID > > > > > > > > ORDER BY [ID!5!hide], [CustomerID!2] > > > > > > > > FOR XML EXPLICIT > > > > > > > > > > > > "David Parenteau" wrote: > > > > > > > > > Hi! I would like the following result returned, but I'm not able to find out > > > > > how... > > > > > Contact1 and COntact2 belong to the same Customer1. I would like the element > > > > > customer duplicated as below. How should I do that? > > > > > > > > > > Note; Currently, I get always 2 contact under the same Customer elements. > > > > > > > > > > SELECT Customer.ID, Contact.ContactID > > > > > FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID > > > > > FOR XML AUTO, ELEMENTS > > > > > > > > > > > > > > > <Customer> > > > > > <CustomerID>Customer1</CustomerID> > > > > > <Contact> > > > > > <ContactID>Contact 1</ContactID> > > > > > </Contact> > > > > > </Customer> > > > > > <Customer> > > > > > <CustomerID>Customer1</CustomerID> > > > > > <Contact> > > > > > <ContactID>Contact 2</ContactID> > > > > > </Contact> > > > > > </Customer> > > > > > > > > > > Thank you! > > > > > > > > > > David Parenteau  
Other interesting topics
Obtain Data from XML column
Bulk load and XSD problem... Obtaining data from an XML column to a document XML Shreading problem XML Collating sequence changing the results header with for xml Exporting to an XML file SQLXML XPath data with apostrophe Returning position() in the results How would I load mulitple XML files into a SQL Server Database. |
|||||||||||||||||||||||
