Running a stored procedure to return all records in a database (phones in this case) and their colour options.
Problem is when a phone has 4 colours for example, it is returning ModelGroup 4 times also.
I know it's probably something obvious, or do i need to put them into a table first?
Stored Procedure:
SELECT PB.ModelGroup as '@group', (SELECT PB1.UID as '@pid', PB1.Brand as '@brand', PB1.Title as '@title', PB1.Friendly_URL_Name as '@url', PB1.Camera as '@camera', PB1.Storage as '@storage', PB1.Screen_Size as '@screensize', PB1.OS as '@os', PB1.Processor as '@chip', PB1.Image1 as '@image', CB.Colour as '@colour' FROM dbo.Phone_Base PB1 INNER JOIN dbo.Colour_Base CB ON CB.UID = PB1.Colour WHERE PB1.Active = 1 AND PB.Title = PB1.Title FOR XML PATH('colours'), TYPE ) FROM dbo.Phone_Base PB WHERE PB.Active = 1 ORDER BY PB.ModelGroup FOR XML PATH('phone'), TYPE XML output:
<phone group="iphone6s16gb"> <colours pid="16" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Space-Grey" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-space-grey.png" colour="Space Grey" /> <colours pid="17" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Gold" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-gold.png" colour="Gold" /> <colours pid="18" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Rose-Gold" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-rose-gold.png" colour="Rose Gold" /> <colours pid="19" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Silver" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-silver.png" colour="Silver" /> </phone> <phone group="iphone6s16gb"> <colours pid="16" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Space-Grey" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-space-grey.png" colour="Space Grey" /> <colours pid="17" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Gold" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-gold.png" colour="Gold" /> <colours pid="18" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Rose-Gold" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-rose-gold.png" colour="Rose Gold" /> <colours pid="19" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Silver" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-silver.png" colour="Silver" /> </phone> <phone group="iphone6s16gb"> <colours pid="16" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Space-Grey" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-space-grey.png" colour="Space Grey" /> <colours pid="17" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Gold" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-gold.png" colour="Gold" /> <colours pid="18" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Rose-Gold" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-rose-gold.png" colour="Rose Gold" /> <colours pid="19" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Silver" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-silver.png" colour="Silver" /> </phone> <phone group="iphone6s16gb"> <colours pid="16" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Space-Grey" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-space-grey.png" colour="Space Grey" /> <colours pid="17" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Gold" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-gold.png" colour="Gold" /> <colours pid="18" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Rose-Gold" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-rose-gold.png" colour="Rose Gold" /> <colours pid="19" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Silver" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-silver.png" colour="Silver" /> </phone> Expected XML Result:
<phone group="iphone6s16gb"> <colours pid="16" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Space-Grey" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-space-grey.png" colour="Space Grey" /> <colours pid="17" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Gold" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-gold.png" colour="Gold" /> <colours pid="18" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Rose-Gold" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-rose-gold.png" colour="Rose Gold" /> <colours pid="19" brand="1" title="iPhone 6S 16GB" url="iPhone-6S-16GB-Silver" camera="12" storage="16GB" screensize="4.7" os="iOS" chip="A9" image="iphone6s-silver.png" colour="Silver" /> </phone>
No comments:
Post a Comment