FOR XML PATH with multiple attributes in child nodes.

• Dennis Post

  SSCarpal Tunnel

  Points: 4473

  More actions

  March 12, 2013 at 7:22 am

  #275154

  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,

  ---

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

  "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

• Mark Cowne

  One Orange Chip

  Points: 26979

  More actions

  March 12, 2013 at 7:57 am

  #1596020

  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

• Dennis Post

  SSCarpal Tunnel

  Points: 4473

  More actions

  March 15, 2013 at 1:22 am

  #1597365

  Thanks Mark !! Works a charm!

  ---

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

  "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort