FOR XML PATH with multiple attributes in child nodes.
-
Trying to get the hang of querying to XML.
Although I can get the result I'm looking for, I'm not sure if it's the recommended way.
Here's all the test code.
/*CREATE TABLE Customers
(
CustID TinyInt Identity(1,1) NOT NULL,
Name Varchar(30) NOT NULL,
Address1 Varchar(50) NOT NULL,
Address2 Varchar(50) NOT NULL,
Postcode Varchar(10) NOT NULL,
Country TinyInt NOT NULL,
CustGrpID TinyInt NOT NULL
)
CREATE TABLE CustomerGroups
(
CustGrpID TinyInt IDENTITY(1,1) NOT NULL,
Name Varchar(20) NOT NULL
)
INSERTCustomers
(Name, Address1, Address2, Postcode, Country, CustGrpID)
SELECT'Cust_One', 'Far', 'Far away', '90210', 255, 1UNION ALL
SELECT'Cust_Two', 'Very', 'Very close', '5060', 1, 2UNION ALL
SELECT'Cust_Three', 'Right', 'Next door', '4321', 1, 1UNION ALL
SELECT'Cust_Four', 'Middle', 'Of nowhere', '666', 127, 2
INSERTCustomerGroups
(Name)
SELECT'Good' UNION ALL
SELECT'Bad'
*/
-- Works but the syntax doesn't look "Correct". Shouldn't have to CAST.
SELECTCG.CustGrpID"@CustGrpID",
CG.Name"@CustGrpName"
,CAST((
SELECTC.CustID"@CustID",
C.Name"@CustName",
C.Address1"Address1",
C.Address2"Address2"
FROMCustomers C
WHEREC.CustGrpID = CG.CustGrpID
FORXML PATH ('Customer'))
AS XML) --CustList --Gives an extra element
FROMCustomerGroups CG
FORXML PATH ('CustomerGroup'), ROOT('CustomersPerCustomerGroup')
How should this be written?
Thanks,
-
Rather than using a cast, replace
FORXML PATH ('Customer'))
with
FORXML PATH ('Customer'),TYPE)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply