parsing XML as one of the column in the table

Question

I have the following table in SQL Server:

CREATE TABLE [dbo].[Test](
    [ID] [int] NULL,
    [Created] [datetime] NULL,
    [XML] [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]
           ,[XML]
          )
     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 data type of TXML is nvarchar(max). If I try to convert nvarcharmax) to type XML then I get an error saying "Unable to switch encoding"

Answer 1

Try this script:

alter table [Test] add RequestType varchar(20) null

go

update [Test]
set RequestType = cast(cast([xml] as varchar(max)) as xml).value('(/*/@RequestType)[1]', 'varchar(20)')