In this post, we will learn different ways to generate XML from SQL table data. First we will create a SQL table.
CREATE TABLE #Car ( CarID INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(100), Make VARCHAR(100), Model INT, Price INT, BodyType VARCHAR(20) ) INSERT INTO #Car(Name, Make, Model, Price, BodyType) VALUES ('Corolla','Toyota',2015, 20000,'Sedan'), ('Civic','Honda',2018, 25000,'Sedan'), ('Passo','Toyota',2012, 18000,'Hatchback'), ('Land Cruiser','Toyota',2017, 40000,'SUV'), ('Corolla','Toyota',2011, 17000,'Sedan'), ('Vitz','Toyota',2014, 15000,'Hatchback'), ('Accord','Honda',2018, 28000,'Sedan'), ('7500','BMW',2015, 50000,'Sedan'), ('Parado','Toyota',2011, 25000,'SUV'), ('C200','Mercedez',2010, 26000,'Sedan'), ('Corolla','Toyota',2014, 19000,'Sedan'), ('Civic','Honda',2015, 20000,'Sedan')
Output is:
Now the different ways to create XML from this table are-
- Option 1:
- SQL Script:
SELECT * FROM #Car FOR XML AUTO
- Output:
<CarID="1" Name="Corolla" Make="Toyota" Model="2015" Price="20000" BodyType="Sedan" /> <CarID="2" Name="Civic" Make="Honda" Model="2018" Price="25000" BodyType="Sedan" /> <CarID="3" Name="Passo" Make="Toyota" Model="2012" Price="18000" BodyType="Hatchback" /> <CarID="4" Name="Land Cruiser" Make="Toyota" Model="2017" Price="40000" BodyType="SUV" /> <CarID="5" Name="Corolla" Make="Toyota" Model="2011" Price="17000" BodyType="Sedan" /> <CarID="6" Name="Vitz" Make="Toyota" Model="2014" Price="15000" BodyType="Hatchback" /> <CarID="7" Name="Accord" Make="Honda" Model="2018" Price="28000" BodyType="Sedan" /> <CarID="8" Name="7500" Make="BMW" Model="2015" Price="50000" BodyType="Sedan" /> <CarID="9" Name="Parado" Make="Toyota" Model="2011" Price="25000" BodyType="SUV" /> <CarID="10" Name="C200" Make="Mercedez" Model="2010" Price="26000" BodyType="Sedan" /> <CarID="11" Name="Corolla" Make="Toyota" Model="2014" Price="19000" BodyType="Sedan" /> <CarID="12" Name="Civic" Make="Honda" Model="2015" Price="20000" BodyType="Sedan" />
- Option 2:
- SQL Script:
SELECT * FROM #Car FOR XML PATH
- Output:
<row> <CarID>1</CarID> <Name>Corolla</Name> <Make>Toyota</Make> <Model>2015</Model> <Price>20000</Price> <BodyType>Sedan</BodyType> </row> <row> <CarID>2</CarID> <Name>Civic</Name> <Make>Honda</Make> <Model>2018</Model> <Price>25000</Price> <BodyType>Sedan</BodyType> </row> <row> <CarID>3</CarID> <Name>Passo</Name> <Make>Toyota</Make> <Model>2012</Model> <Price>18000</Price> <BodyType>Hatchback</BodyType> </row> <row> <CarID>4</CarID> <Name>Land Cruiser</Name> <Make>Toyota</Make> <Model>2017</Model> <Price>40000</Price> <BodyType>SUV</BodyType> </row> <row> <CarID>5</CarID> <Name>Corolla</Name> <Make>Toyota</Make> <Model>2011</Model> <Price>17000</Price> <BodyType>Sedan</BodyType> </row> <row> <CarID>6</CarID> <Name>Vitz</Name> <Make>Toyota</Make> <Model>2014</Model> <Price>15000</Price> <BodyType>Hatchback</BodyType> </row> <row> <CarID>7</CarID> <Name>Accord</Name> <Make>Honda</Make> <Model>2018</Model> <Price>28000</Price> <BodyType>Sedan</BodyType> </row> <row> <CarID>8</CarID> <Name>7500</Name> <Make>BMW</Make> <Model>2015</Model> <Price>50000</Price> <BodyType>Sedan</BodyType> </row> <row> <CarID>9</CarID> <Name>Parado</Name> <Make>Toyota</Make> <Model>2011</Model> <Price>25000</Price> <BodyType>SUV</BodyType> </row> <row> <CarID>10</CarID> <Name>C200</Name> <Make>Mercedez</Make> <Model>2010</Model> <Price>26000</Price> <BodyType>Sedan</BodyType> </row> <row> <CarID>11</CarID> <Name>Corolla</Name> <Make>Toyota</Make> <Model>2014</Model> <Price>19000</Price> <BodyType>Sedan</BodyType> </row> <row> <CarID>12</CarID> <Name>Civic</Name> <Make>Honda</Make> <Model>2015</Model> <Price>20000</Price> <BodyType>Sedan</BodyType> </row>
- Option 3:
- SQL Script:
SELECT * FROM #Car FOR XML PATH ('Car')
- Output:
<Car> <CarID>1</CarID> <Name>Corolla</Name> <Make>Toyota</Make> <Model>2015</Model> <Price>20000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>2</CarID> <Name>Civic</Name> <Make>Honda</Make> <Model>2018</Model> <Price>25000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>3</CarID> <Name>Passo</Name> <Make>Toyota</Make> <Model>2012</Model> <Price>18000</Price> <BodyType>Hatchback</BodyType> </Car> <Car> <CarID>4</CarID> <Name>Land Cruiser</Name> <Make>Toyota</Make> <Model>2017</Model> <Price>40000</Price> <BodyType>SUV</BodyType> </Car> <Car> <CarID>5</CarID> <Name>Corolla</Name> <Make>Toyota</Make> <Model>2011</Model> <Price>17000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>6</CarID> <Name>Vitz</Name> <Make>Toyota</Make> <Model>2014</Model> <Price>15000</Price> <BodyType>Hatchback</BodyType> </Car> <Car> <CarID>7</CarID> <Name>Accord</Name> <Make>Honda</Make> <Model>2018</Model> <Price>28000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>8</CarID> <Name>7500</Name> <Make>BMW</Make> <Model>2015</Model> <Price>50000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>9</CarID> <Name>Parado</Name> <Make>Toyota</Make> <Model>2011</Model> <Price>25000</Price> <BodyType>SUV</BodyType> </Car> <Car> <CarID>10</CarID> <Name>C200</Name> <Make>Mercedez</Make> <Model>2010</Model> <Price>26000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>11</CarID> <Name>Corolla</Name> <Make>Toyota</Make> <Model>2014</Model> <Price>19000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>12</CarID> <Name>Civic</Name> <Make>Honda</Make> <Model>2015</Model> <Price>20000</Price> <BodyType>Sedan</BodyType> </Car>
- Option 4:
- SQL Script:
SELECT * FROM #Car FOR XML PATH ('Car'), ROOT('Cars')
- Output:
<Cars> <Car> <CarID>1</CarID> <Name>Corolla</Name> <Make>Toyota</Make> <Model>2015</Model> <Price>20000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>2</CarID> <Name>Civic</Name> <Make>Honda</Make> <Model>2018</Model> <Price>25000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>3</CarID> <Name>Passo</Name> <Make>Toyota</Make> <Model>2012</Model> <Price>18000</Price> <BodyType>Hatchback</BodyType> </Car> <Car> <CarID>4</CarID> <Name>Land Cruiser</Name> <Make>Toyota</Make> <Model>2017</Model> <Price>40000</Price> <BodyType>SUV</BodyType> </Car> <Car> <CarID>5</CarID> <Name>Corolla</Name> <Make>Toyota</Make> <Model>2011</Model> <Price>17000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>6</CarID> <Name>Vitz</Name> <Make>Toyota</Make> <Model>2014</Model> <Price>15000</Price> <BodyType>Hatchback</BodyType> </Car> <Car> <CarID>7</CarID> <Name>Accord</Name> <Make>Honda</Make> <Model>2018</Model> <Price>28000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>8</CarID> <Name>7500</Name> <Make>BMW</Make> <Model>2015</Model> <Price>50000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>9</CarID> <Name>Parado</Name> <Make>Toyota</Make> <Model>2011</Model> <Price>25000</Price> <BodyType>SUV</BodyType> </Car> <Car> <CarID>10</CarID> <Name>C200</Name> <Make>Mercedez</Make> <Model>2010</Model> <Price>26000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>11</CarID> <Name>Corolla</Name> <Make>Toyota</Make> <Model>2014</Model> <Price>19000</Price> <BodyType>Sedan</BodyType> </Car> <Car> <CarID>12</CarID> <Name>Civic</Name> <Make>Honda</Make> <Model>2015</Model> <Price>20000</Price> <BodyType>Sedan</BodyType> </Car> </Cars>
- Option 5:
- SQL Script:
SELECT CarID AS [@CarID], Name AS [CarInfo/@Name], Make [CarInfo/@Make], Model [CarInfo/Model], Price, BodyType FROM #Car FOR XML PATH ('Car'), ROOT('Cars')
- Output:
<Cars> <Car CarID="1"> <CarInfo Name="Corolla" Make="Toyota"> <Model>2015</Model> </CarInfo> <Price>20000</Price> <BodyType>Sedan</BodyType> </Car> <Car CarID="2"> <CarInfo Name="Civic" Make="Honda"> <Model>2018</Model> </CarInfo> <Price>25000</Price> <BodyType>Sedan</BodyType> </Car> <Car CarID="3"> <CarInfo Name="Passo" Make="Toyota"> <Model>2012</Model> </CarInfo> <Price>18000</Price> <BodyType>Hatchback</BodyType> </Car> <Car CarID="4"> <CarInfo Name="Land Cruiser" Make="Toyota"> <Model>2017</Model> </CarInfo> <Price>40000</Price> <BodyType>SUV</BodyType> </Car> <Car CarID="5"> <CarInfo Name="Corolla" Make="Toyota"> <Model>2011</Model> </CarInfo> <Price>17000</Price> <BodyType>Sedan</BodyType> </Car> <Car CarID="6"> <CarInfo Name="Vitz" Make="Toyota"> <Model>2014</Model> </CarInfo> <Price>15000</Price> <BodyType>Hatchback</BodyType> </Car> <Car CarID="7"> <CarInfo Name="Accord" Make="Honda"> <Model>2018</Model> </CarInfo> <Price>28000</Price> <BodyType>Sedan</BodyType> </Car> <Car CarID="8"> <CarInfo Name="7500" Make="BMW"> <Model>2015</Model> </CarInfo> <Price>50000</Price> <BodyType>Sedan</BodyType> </Car> <Car CarID="9"> <CarInfo Name="Parado" Make="Toyota"> <Model>2011</Model> </CarInfo> <Price>25000</Price> <BodyType>SUV</BodyType> </Car> <Car CarID="10"> <CarInfo Name="C200" Make="Mercedez"> <Model>2010</Model> </CarInfo> <Price>26000</Price> <BodyType>Sedan</BodyType> </Car> <Car CarID="11"> <CarInfo Name="Corolla" Make="Toyota"> <Model>2014</Model> </CarInfo> <Price>19000</Price> <BodyType>Sedan</BodyType> </Car> <Car CarID="12"> <CarInfo Name="Civic" Make="Honda"> <Model>2015</Model> </CarInfo> <Price>20000</Price> <BodyType>Sedan</BodyType> </Car> </Cars>
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.