Thursday, February 24, 2022

SQL Table To XML

Hello Friends,
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-
  1. Option 1:
    1. SQL Script:
      1. SELECT * FROM #Car FOR XML AUTO
    2. Output:
      1. <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" /> 
  2. Option 2:
      1. SQL Script:
        1. SELECT * FROM #Car FOR XML PATH
      2. Output:
        1. <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>
    1. Option 3:
        1. SQL Script:
          1. SELECT * FROM #Car FOR XML PATH ('Car')
        2. Output:
          1. <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>
      1. Option 4:
          1. SQL Script:
            1. SELECT * FROM #Car FOR XML PATH ('Car'), ROOT('Cars')
          2. Output:
            1. <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>
        1. Option 5:
          1. SQL Script:
            1. SELECT
	CarID AS [@CarID],
    Name  AS [CarInfo/@Name],
    Make [CarInfo/@Make],
    Model [CarInfo/Model],
    Price,
    BodyType
FROM #Car 
FOR XML PATH ('Car'), ROOT('Cars')
          2. Output:
            1. <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>
        With this, I am concluding this post. Happy Coding !!! Will see you again with some new topics. Stay Safe ! Stay Healthy !
        at
        Labels: ,

        No comments:

        Post a Comment

        Note: Only a member of this blog may post a comment.

        Subscribe to: Post Comments (Atom)