asked
Creating XML using For XML
I have 3 tables Supplier,Supplier_derails and Supplier_contacts.Now i want to generate XML for this 3 tables in below formart 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 ABC 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 900 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 800 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 500 32828 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 32829 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 32830 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 When i am doing joins on this three tables i am getting XML where is becoming child node of . Please help
The XML you have provided is not valid and is nonsense if you take a look on the Element opening and closing like `xxxx`. Can you provide the tables metadata? It will be much easier to construct the query.
it was a typo error. I apologies. Following is the correct one 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 ABC 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 900 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 800 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 500 32828 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 32829 01BFEE74-AEA4-43EB-A9D9-60529E5AF936 32830 01BFEE74-AEA4-43EB-A9D9-60529E5AF936
Hi, here is he query which should return the exact XML you are requesting. DECLARE @Supplier TABLE ( supplier_id uniqueidentifier NOT NULL PRIMARY KEY, supplier_name varchar(10) ) DECLARE @Supplier_details TABLE ( supplier_id uniqueidentifier NOT NULL, supplierdetails_code int ) DECLARE @Supplier_contacts TABLE ( supplier_id uniqueidentifier NOT NULL, supplier_contact_code int ) DECLARE @id uniqueidentifier SET @id = NEWID(); INSERT INTO @supplier(supplier_id, supplier_name) SELECT @id, 'ABC' INSERT INTO @Supplier_details(supplier_id, supplierdetails_code) SELECT @id, 900 UNION ALL SELECT @id, 800 UNION ALL SELECT @id, 500 INSERT INTO @Supplier_contacts(supplier_id, supplier_contact_code) SELECT @id, 32828 UNION ALL SELECT @id, 32829 UNION ALL SELECT @id, 32830 SELECT S.supplier_id, S.supplier_name, CAST((SELECT * FROM @Supplier_details d WHERE d.supplier_id = S.supplier_id FOR XML PATH('supplierdetails')) AS XML), CAST((SELECT * FROM @Supplier_contacts c WHERE c.supplier_id = S.supplier_id FOR XML PATH('supplier_contact')) AS XML) FROM @Supplier S FOR XML PATH('supplier') If you have any comments related tot he syntax, don't hesitate to post them here.
