PowerApps, Power Automate, SharePoint, Dynamics 365, Azure, C#: February 2022

Friday, February 25, 2022

String To Table in SQL

Hello Friends,

Today, we are talking about another topic in SQL. Today, we will convert delimiter based string into table. The scenario is, we have a row and cell delimited string, where each cell value is separated by a delimiter (say $) and each row is again separated by a delimiter (say |). Next, this string may have some garbage value- means, such values, that we don't want to insert into SQL table. For example, we have a string "FirstName$LastName|". If LastName is blank or NULL, don't insert into table. Also, we need to add an identity column as well. Let's see how do we do it.

SQL Script:

DECLARE @Country NVARCHAR(MAX) = 'India'
DECLARE @Proposal1String NVARCHAR(MAX) = 'Sachin$Jain|Ram$Vinay|Lokesh$Kumar|Kulbhushan$|Rajeev$Sharma|Manoj$Kumar|'
DECLARE @DelimiterCell CHAR(1) = '$'
DECLARE @DelimiterRow CHAR(1) = '|'
DECLARE @ResultTable TABLE(SplittedRow NVARCHAR(MAX))

DECLARE @StartPosition INT, @EndPosition INT
SELECT @StartPosition = 1, @EndPosition = CHARINDEX(@DelimiterRow, @Proposal1String)
WHILE @StartPosition < LEN(@Proposal1String) + 1
BEGIN
	IF @EndPosition = 0 
		SET @EndPosition = LEN(@Proposal1String) + 1
		INSERT INTO @ResultTable(SplittedRow) 
		VALUES(SUBSTRING(@Proposal1String, @StartPosition, @EndPosition - @StartPosition))
		SET @StartPosition = @EndPosition + 1
		SET @EndPosition = CHARINDEX(@DelimiterRow, @Proposal1String, @StartPosition)
END

SELECT * FROM @ResultTable

DECLARE @FinalTable TABLE (SrNo INT IDENTITY(1,1), FName NVARCHAR(MAX), LName NVARCHAR(MAX), Country NVARCHAR(MAX))

INSERT INTO @FinalTable (FName, LName, Country) 
SELECT 
	SUBSTRING(SplittedRow, 0, CHARINDEX(@DelimiterCell, SplittedRow, 0)),
	SUBSTRING(SplittedRow, CHARINDEX(@DelimiterCell, SplittedRow, 0)+1, LEN(SplittedRow)),
	@Country
FROM @ResultTable

SELECT * FROM @FinalTable
DELETE FROM @FinalTable WHERE LName IS NULL OR LName = ''
SELECT * FROM @FinalTable

Output:

If you wish to do some activity upon the splitted table data and based upon that activity, you wish to either UPDATE the record in actual table or INSERT the new record then-

DECLARE @RowNumber INT = 1
DECLARE @MaxSrNo INT = (SELECT MAX(SrNo) FROM @FinalTable)
    
WHILE @RowNumber <= @MaxSrNo
BEGIN
	--DO YOUR ACTIVITY HERE
	--PRINT @RowNumber
	SET @RowNumber = @RowNumber + 1;
END

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

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-

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>

With this, I am concluding this post. Happy Coding !!! Will see you again with some new topics. Stay Safe ! Stay Healthy !

Header Navigation

Friday, February 25, 2022

String To Table in SQL

Thursday, February 24, 2022

SQL Table To XML