Format Using FOR Xml Explicit Mode

 

--STEP 1 CREATE A TABLE

DECLARE @AutoMobile AS TABLE (ID int IDENTITY(1,1) NOT NULL,Customer varchar(100),Make Varchar(100),Model Varchar(100),Year int)

 

--STEP2 INSERT VALUES

INSERT INTO @AutoMobile VALUES('Ron','Audi','Audi 100',2005)

INSERT INTO @AutoMobile VALUES('Jhon','Audi','Audi Allroad',2005)

INSERT INTO @AutoMobile VALUES('Chris','Audi','Audi Cabriolet',2006)

 

INSERT INTO @AutoMobile VALUES('Bruce','Chrysler','Chrysler LHS',2005)

INSERT INTO @AutoMobile VALUES('Lee','Chrysler','Chrysler New Yorker',2006)

INSERT INTO @AutoMobile VALUES('Barry','Chrysler','Chrysler PT Cruiser',2007)

INSERT INTO @AutoMobile VALUES('Bob','Chrysler','Chrysler Sebring',2005)

 

 

INSERT INTO @AutoMobile VALUES('Tom','Mercury','Mercury Topaz',2007)

INSERT INTO @AutoMobile VALUES('Poo','Mercury','Mercury Tracer',2007)

INSERT INTO @AutoMobile VALUES('Nee','Mercury','Mercury Zephyr',2006)

INSERT INTO @AutoMobile VALUES('Pops','Mercury','Mercury Villager',2007)

 

--STEP 3

--SELECT 1 as tag,null as parent,ID  FROM @AutoMobile FOR xml EXPLICIT

--You will get a SQL Error: FOR XML EXPLICIT query contains the invalid column name 'ID'. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive integer.

         

--STEP 4: Create XML Nodes on column ID

--add tag name and its tag id

SELECT 1 as tag,null as parent,ID as [id!1] FROM @AutoMobile FOR xml EXPLICIT

 

--STEP 5: Create XML Nodes on column ID and column Model

SELECT 1 as tag,null as parent,ID as [id!1],NULL as [Model!2] FROM @AutoMobile

UNION  ALL

SELECT 2 as tag,1 as parent,NULL as [id!1],Model as [Model!2] FROM @AutoMobile FOR xml EXPLICIT

 

--STEP 6: Now form a proper Xml Document with all details

SELECT 1 as tag,null as parent,null as  [auto!1!element],NULL as [Model!2!name],null as [Model!2!Customer!element]

UNION  ALL

SELECT 2 as tag,1 as parent,null as [auto!1!element],Model as [Model!2!name],Customer as [Model!2!Customer!element] FROM @AutoMobile  WHERE Make='Mercury'

for xml explicit