parsing XML as one of the column in the table

Anjali Agarwal 1,386 Reputation points
2023-05-20T01:34:22.4+00:00

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"
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,264 questions
{count} votes

Accepted answer
  1. Viorel 114.4K Reputation points
    2023-05-20T05:33:22.9866667+00:00

    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)')
    
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful