Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FOR XML PATH with multiple attributes in child nodes. Expand / Collapse
Author
Message
Posted Tuesday, March 12, 2013 7:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 06, 2014 1:35 AM
Points: 178, Visits: 547
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
)

INSERT Customers
(Name, Address1, Address2, Postcode, Country, CustGrpID)
SELECT 'Cust_One', 'Far', 'Far away', '90210', 255, 1 UNION ALL
SELECT 'Cust_Two', 'Very', 'Very close', '5060', 1, 2 UNION ALL
SELECT 'Cust_Three', 'Right', 'Next door', '4321', 1, 1 UNION ALL
SELECT 'Cust_Four', 'Middle', 'Of nowhere', '666', 127, 2

INSERT CustomerGroups
(Name)
SELECT 'Good' UNION ALL
SELECT 'Bad'
*/

-- Works but the syntax doesn't look "Correct". Shouldn't have to CAST.
SELECT CG.CustGrpID "@CustGrpID",
CG.Name "@CustGrpName"
,CAST((
SELECT C.CustID "@CustID",
C.Name "@CustName",
C.Address1 "Address1",
C.Address2 "Address2"
FROM Customers C
WHERE C.CustGrpID = CG.CustGrpID
FOR XML PATH ('Customer'))
AS XML) --CustList --Gives an extra element
FROM CustomerGroups CG
FOR XML PATH ('CustomerGroup'), ROOT('CustomersPerCustomerGroup')

How should this be written?
Thanks,




For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1429798
Posted Tuesday, March 12, 2013 7:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Rather than using a cast, replace

FOR XML PATH ('Customer'))

with

FOR XML PATH ('Customer'),TYPE)


____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1429823
Posted Friday, March 15, 2013 1:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 06, 2014 1:35 AM
Points: 178, Visits: 547
Thanks Mark !! Works a charm!





For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1431383
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse