Showing posts with label XML. Show all posts
Showing posts with label XML. Show all posts

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 No comments:
        Labels: ,

        SQL and XML

        Hello Friends,
        Today, we are discussing some of the XML options used in SQL. These methods will take XML  string as input and converts into tabular data. This tabular data, you may insert into the SQL table or you may use as per the requirement.

        1. Option 1:
          1. XML Sample:
            1. <root>
	<Employee>
		<FirstName>Sachin</FirstName>
		<LastName>Jain</LastName>
		<City>Delhi</City>
		<Country>India</Country>
	</Employee>
	<Employee>
		<FirstName>Lokesh</FirstName>
		<LastName>Kumar</LastName>
		<City>Alwar</City>
		<Country>India</Country>
	</Employee>
	<Employee>
		<FirstName>Ram</FirstName>
		<LastName>Vinay</LastName>
		<City>Varanasi</City>
		<Country>India</Country>
	</Employee>
</root>
          2. SQL Script:
            1. DECLARE @varXMLString XML = CAST('
<root>
	<Employee>
		<FirstName>Sachin</FirstName>
		<LastName>Jain</LastName>
		<City>Delhi</City>
		<Country>India</Country>
	</Employee>
	<Employee>
		<FirstName>Lokesh</FirstName>
		<LastName>Kumar</LastName>
		<City>Alwar</City>
		<Country>India</Country>
	</Employee>
	<Employee>
		<FirstName>Ram</FirstName>
		<LastName>Vinay</LastName>
		<City>Varanasi</City>
		<Country>India</Country>
	</Employee>
</root>' AS XML)


SELECT  
	Tbl.Col.value('FirstName[1]', 'NVARCHAR(MAX)') AS 'First Name',  
	Tbl.Col.value('LastName[1]', 'NVARCHAR(MAX)') AS 'Last Name',  
	Tbl.Col.value('City[1]', 'NVARCHAR(MAX)') AS 'City',
	Tbl.Col.value('Country[1]', 'NVARCHAR(MAX)') AS 'Country'
FROM @varXMLString.nodes('root/Employee') Tbl(Col)
          3. Output:
        2. Option 2:
          1. XML Sample:
            1. <Employee FirstName="Sachin" LastName="Jain" City="Delhi" Country="India" />
<Employee FirstName="Lokesh" LastName="Kumar" City="Alwar" Country="India" />
<Employee FirstName="Ram" LastName="Vinay" City="Varanasi" Country="India" />
          2. SQL Script:
            1. DECLARE @varXMLString XML = CAST('
<Employee FirstName="Sachin" LastName="Jain" City="Delhi" Country="India" />
<Employee FirstName="Lokesh" LastName="Kumar" City="Alwar" Country="India" />
<Employee FirstName="Ram" LastName="Vinay" City="Varanasi" Country="India" />
' AS XML)

SELECT  
       Tbl.Col.value('@FirstName', 'NVARCHAR(MAX)') AS 'First Name',
       Tbl.Col.value('@LastName', 'NVARCHAR(MAX)') AS 'Last Name',
       Tbl.Col.value('@City', 'NVARCHAR(MAX)') AS 'City',
       Tbl.Col.value('@Country', 'NVARCHAR(MAX)') AS 'Country'

FROM   @varXMLString.nodes('Employee') Tbl(Col)
          3. Output:
        3. Option 3:
          1. XML Sample:
            1. <root>
	<Employee>
		<FirstName>Sachin</FirstName>
		<LastName>Jain</LastName>
		<City>Delhi</City>
		<Country>India</Country>
	</Employee>
	<Employee>
		<FirstName>Lokesh</FirstName>
		<LastName>Kumar</LastName>
		<City>Alwar</City>
		<Country>India</Country>
	</Employee>
	<Employee>
		<FirstName>Ram</FirstName>
		<LastName>Vinay</LastName>
		<City>Varanasi</City>
		<Country>India</Country>
	</Employee>
</root>
          2. SQL Script:
            1. DECLARE @varXMLString XML = 
'<root>
	<Employee>
		<FirstName>Sachin</FirstName>
		<LastName>Jain</LastName>
		<City>Delhi</City>
		<Country>India</Country>
	</Employee>
	<Employee>
		<FirstName>Lokesh</FirstName>
		<LastName>Kumar</LastName>
		<City>Alwar</City>
		<Country>India</Country>
	</Employee>
	<Employee>
		<FirstName>Ram</FirstName>
		<LastName>Vinay</LastName>
		<City>Varanasi</City>
		<Country>India</Country>
	</Employee>
</root>'

DECLARE @handle INT  

EXEC sp_xml_preparedocument @handle OUTPUT, @varXMLString  

SELECT FirstName AS 'First Name', LastName AS 'Last Name', City, Country
FROM OPENXML(@handle, '/root/Employee', 2)  
WITH (
	FirstName NVARCHAR(MAX),
	LastName NVARCHAR(MAX),
	City NVARCHAR(MAX),
	Country NVARCHAR(MAX)
)
          3. Output:
        4. Option 4:
          1. XML Sample:
            1. <root>
	<FirstName>Sachin</FirstName>
	<FirstName>Lokesh</FirstName>
	<FirstName>Ram</FirstName>
</root>
          2. SQL Script:
            1. DECLARE @varXMLString XML = 
'<root>
	<FirstName>Sachin</FirstName>
	<FirstName>Lokesh</FirstName>
	<FirstName>Ram</FirstName>
</root>'

SELECT x.y.value('.','NVARCHAR(MAX)') AS 'First Name'
FROM @varXMLString.nodes('/root/FirstName') AS x(y)
          3. Output:
        5. Option 5:
          1. XML Sample:
            1. <root>
	<Employee FirstName="Sachin" />
	<Employee FirstName="Lokesh" />
	<Employee FirstName="Ram" />
</root>
          2. SQL Script:
            1. DECLARE @varXMLString XML = 
'<root>
	<Employee FirstName="Sachin" />
	<Employee FirstName="Lokesh" />
	<Employee FirstName="Ram" />
</root>'

SELECT x.y.value('@FirstName','NVARCHAR(MAX)') AS 'First Name'
FROM @varXMLString.nodes('/root/Employee') AS x(y)
          3. Output:
        6. Option 6:
          1. XML Sample:
            1. <root>
	<Employee FirstName="Sachin" />
	<Employee FirstName="Lokesh" />
	<Employee FirstName="Ram" />
</root>
          2. SQL Script:
            1. DECLARE @varXMLString XML = 
'<root>
	<Employee FirstName="Sachin" />
	<Employee FirstName="Lokesh" />
	<Employee FirstName="Ram" />
</root>'

SELECT x.y.value('.','NVARCHAR(MAX)') AS 'First Name'
FROM @varXMLString.nodes('/root/Employee/@FirstName') AS x(y)
          3. Output:
        With this, I am concluding this post.
        Happy Coding !!!
        Will see you again with some new topics.

        Stay Safe !
        Stay Healthy !
        at No comments:
        Labels: ,