SQL Puzzle | The XML Node Copy Puzzle

In this puzzle you have to read the XML using T-SQL. And if you get the Loading area name WCMEX then you have to copy it and add a new node called WCMEX1.

Please check out the sample input values and sample expected output below.

Please let me know if you have any easy method other than this.

Sample Input

<loadingAreas>
    <LoadingArea>
      <areaName>Antartic</areaName>
      <portId>0021</portId>
      <portName>Houston</portName>
    </LoadingArea>
      <LoadingArea>
      <areaName>WCMEX</areaName>
      <portId>0004</portId>
      <portName>Manzanillo, Mexico</portName>
    </LoadingArea>
  </loadingAreas>

Expected Output

<LoadingAreas>
  <LoadingArea>
    <areaName>Antartic</areaName>
    <portId>0021</portId>
    <portName>Houston</portName>
  </LoadingArea>
  <LoadingArea>
    <areaName>WCMEX</areaName>
    <portId>0004</portId>
    <portName>Manzanillo, Mexico</portName>
  </LoadingArea>
  <LoadingArea>
    <areaName>WCMEX1</areaName>
    <portId>0004</portId>
    <portName>Manzanillo, Mexico</portName>
  </LoadingArea>
</LoadingAreas>

Script

Use below script to create table and insert sample data into it.

--

SELECT CAST(
'
  <loadingAreas>
    <LoadingArea>
      <areaName>Antartic</areaName>
      <portId>0021</portId>
      <portName>Houston</portName>
    </LoadingArea>
      <LoadingArea>
      <areaName>WCMEX</areaName>
      <portId>0004</portId>
      <portName>Manzanillo, Mexico</portName>
    </LoadingArea>
  </loadingAreas>

' AS XML ) k

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

SELECT CAST(
'
  <loadingAreas>
    <LoadingArea>
      <areaName>Antartic</areaName>
      <portId>0021</portId>
      <portName>Houston</portName>
    </LoadingArea>
      <LoadingArea>
      <areaName>WCMEX</areaName>
      <portId>0004</portId>
      <portName>Manzanillo, Mexico</portName>
    </LoadingArea>
  </loadingAreas>

' AS XML ) k INTO InputXML1

SELECT * FROM 
(
	SELECT 
		 a.b.value('areaName[1]', 'VARCHAR(100)') AS 'areaName'
		,a.b.value('(portId)[1]', 'VARCHAR(100)') AS 'portId'
		,a.b.value('(portName)[1]', 'VARCHAR(100)') AS 'portName'	 
	FROM InputXML1 as s
		OUTER APPLY s.k.nodes('loadingAreas/LoadingArea') as a(b)
	UNION ALL
	SELECT 
		 'WCMEX1' as 'areaName'
		,a.b.value('(portId)[1]', 'VARCHAR(100)') AS 'portId'
		,a.b.value('(portName)[1]', 'VARCHAR(100)') AS 'portName'	 
	FROM InputXML1 as s
		OUTER APPLY s.k.nodes('loadingAreas/LoadingArea') as a(b)
	WHERE a.b.value('areaName[1]', 'VARCHAR(100)') = 'WCMEX'
)u FOR XML PATH ('LoadingArea'), root ('LoadingAreas'); 

--

Output

--

<LoadingAreas>
  <LoadingArea>
    <areaName>Antartic</areaName>
    <portId>0021</portId>
    <portName>Houston</portName>
  </LoadingArea>
  <LoadingArea>
    <areaName>WCMEX</areaName>
    <portId>0004</portId>
    <portName>Manzanillo, Mexico</portName>
  </LoadingArea>
  <LoadingArea>
    <areaName>WCMEX1</areaName>
    <portId>0004</portId>
    <portName>Manzanillo, Mexico</portName>
  </LoadingArea>
</LoadingAreas>

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com