SQL Server Parsing XML in database column and putting it as separate column

egmofgnx 于 2023-05-21 发布在其他

关注(0)|答案(2)|浏览(133)

I have the following table in SQL Server:

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

This is what I tried:

SELECT [ID]
      ,[Created]
      ,[XML]
      , [type] =Node.[XML].value('RequestType', 'varchar(100)')
  FROM [db_Nitu].[dbo].[Test]

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 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

来源：https://stackoverflow.com/questions/76292658/parsing-xml-in-database-column-and-putting-it-as-separate-column

2条答案

按热度按时间

ekqde3dh1#

One way to do it is:

select *, cast(REPLACE(CAST(xml AS NVARCHAR(MAX)), '<?xml version="1.0" encoding="UTF-8"?>', '') as xml).value('Transaction[1]/@RequestType', 'nvarchar(100)') AS transType
from [dbo].[Test]

One has to remove the encoding declaration and then you just get the requestType by using XPATH.

You could also create a computed column that calculates the value on the fly, but one problem is that XML methods don't seem to be allowed, so you need to wrap the above logic into a function

It would behoove you to change the datatype of xml column to XML , or at least to NVARCHAR(MAX) , NTEXT is very 90s.

赞(0）回复(0）举报 2023-05-21

fdbelqdn2#

This is called "promoting properties" from the XML. And you can do it with a persisted, computed column, like this:

use tempdb
go
CREATE TABLE [dbo].[Test](
    [ID] int NULL,
    [Created] datetime NULL,
    [XML] xml NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

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
create or alter function ExtractRequestType(@doc xml)
returns varchar(20)
with schemabinding
as
begin
  return @doc.value('(/Transaction/@RequestType)[1]', 'varchar(20)')
end
go
alter table test add RequestType as dbo.ExtractRequestType([XML]) persisted
go

select ID, Created, RequestType  
from Test

returns

ID          Created                 RequestType
----------- ----------------------- --------------------
1234        2020-12-23 00:00:00.000 TesType

(1 row affected)

赞(0）回复(0）举报 2023-05-21

我来回答

SQL Server Parsing XML in database column and putting it as separate column

2条答案

相关问题

热门标签

最新问答