Parsing XML in database column and putting it as separate column

Question

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"

Does this answer your question? [Parse XML data from a column in SQL Server](https://stackoverflow.com/questions/71429625/parse-xml-data-from-a-column-in-sql-server) — Tony, May 19 '23 at 22:24
No, This question does not help me because my XML pattern is different. Also, there is no accepted answer in the question because the answer is not explained properly. — Anjali, May 19 '23 at 22:28
Does either of these questions help? [SQL: How can I get the value of an attribute in XML datatype?](https://stackoverflow.com/questions/8808652/sql-how-can-i-get-the-value-of-an-attribute-in-xml-datatype), [SQL Server XML parsing first node attributes](https://stackoverflow.com/questions/7953896/sql-server-xml-parsing-first-node-attributes) — Tony, May 19 '23 at 22:43
@Anjali - I didn't down vote you either but others may have because your original question did not show any research effort; even though you may have tried the solutions I found it was not evident in your question. I appreciate you have been a member for a number of years, and have asked quite a few questions, but it's always good to remember the advice in the [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) help section :) — Tony, May 19 '23 at 23:20

score 3 · Accepted Answer · edited May 19 '23 at 23:00

3

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.

edited May 19 '23 at 23:00

Dale K

25,246
15
42
71

answered May 19 '23 at 22:35

siggemannen

3,884
2
6
24

Try again, you can't do Just @RequestType, since it's under the Transaction-node, see what my code does – siggemannen May 19 '23 at 22:46
You can also check @David Browne's answer – siggemannen May 19 '23 at 22:46
It works, I was doing something wrong – Anjali May 20 '23 at 05:45
1

Note the removal of the encoding attribute as explained in the link I posted below the other answer. – Dale K May 20 '23 at 07:03

score 2 · Answer 2 · answered May 19 '23 at 22:42

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)

when I run the alter table command from you answer above. I get the error saying - "XML parsing: unable to switch the encoding. The statement has been terminated" — Anjali, May 20 '23 at 00:59
@Anjali please check [this question](https://stackoverflow.com/questions/44892059/msg-9402-level-16-state-1-line-9-xml-parsing-line-1-character-38-unable-to) which explains why you are getting the error you are getting. Fundamentally you 1) Shouldn't be using the `ntext` datatype and 2) If you are storing your XML as a string instead of XML, then you can't use the encoding type in your XML because the string definition determines the encoding type - hence resulting in the error you are seeing. — Dale K, May 20 '23 at 04:14

Parsing XML in database column and putting it as separate column

2 Answers2