Parsing XML file in sql and putting it as one of the column

1

I have the following table in MS SQL :

CREATE TABLE [dbo].[Test](
    [ID] [int] NULL,
    [Created] [datetime] NULL,
    [TXML] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I have the following data in this table. I have around 1000 rows.

INSERT INTO [dbo].[Test]
           ([ID]
           ,[Created]
           ,[TXML]
          )
     VALUES
           (1234
           ,'12/23/2020'
           ,'<?xml version="1.0" encoding="UTF-8"?><Transaction ItemId="12345" Type="CopyRequest" RequestType="TesType" Description="8" NameOnRecord="test" Paid="false" Printed="false" Status="test1" VoidReason="" ProcessedLocationId="" ProcessedBy="" ProcessedDate="" ><Parameters><Parameter Name="DocumentId" Value="999" Type="1"/><Parameter Name="ActionDefId" Value="11222" Type="1"/><Parameter Name="UseRequestForm" Value="true" Type="4"/><Parameter Name="RequestedDocumentTitle" Value="77777" Type="1"/><Parameter Name="test1" Value="false" Type="4"/><Parameter Name="Type" Value="IN_OFFICE" Type="1"/><Parameter Name="test" Value="false" Type="1"/></Parameters></Transaction>')
GO

I want to parse the XML above in a query and extract RequestType from XML and put the value of "RequestType" as a separate column in the "Test" table. How can I achieve this in SQL Server. This is how the XML looks like :

<?xml version="1.0" encoding="UTF-8"?>
<Transaction ItemId="12345" Type="CopyRequest" RequestType="TesType" Description="8" NameOnRecord="test" Paid="false" Printed="false" Status="test1" VoidReason="" ProcessedLocationId="" ProcessedBy="" ProcessedDate="">
   <Parameters>
      <Parameter Name="DocumentId" Value="999" Type="1" />
      <Parameter Name="ActionDefId" Value="11222" Type="1" />
      <Parameter Name="UseRequestForm" Value="true" Type="4" />
      <Parameter Name="RequestedDocumentTitle" Value="77777" Type="1" />
      <Parameter Name="test1" Value="false" Type="4" />
      <Parameter Name="Type" Value="IN_OFFICE" Type="1" />
      <Parameter Name="test" Value="false" Type="1" />
   </Parameters>
</Transaction>

I want the requestType from the XML as a separate column in the Test table. The Type of TXML is nvarchar(MAX) and the header has encoding="UTF-8

2

There may be a better way:

WITH ToXMLCols
AS
(
	SELECT ID, Created
		,CAST(TXML AS xml) XMLCol
	FROM dbo.Test
)
SELECT ID, Created
	,X.Trans.value('@RequestType', 'nvarchar(20)') AS RequestType
FROM ToXMLCols C
	CROSS APPLY C.XMLCol.nodes('/Transaction') as X (Trans);
1 Like
3

Another way, which may not be any more efficient or better, but more compact perhaps

SELECT ID,
       Created,
       TXML ,
       c3.value('(Parameters/Parameter[@Name="Type"])[1]/@Value','Varchar(128)')
FROM 
    dbo.Test AS t
    CROSS APPLY (SELECT CAST(CAST(txml AS VARCHAR(MAX)) AS XML) ) t2(c2)
    CROSS APPLY c2.nodes('/Transaction') t3(c3)
1 Like
4

Thank you!