How to extract values from a XML column in SQL and insert into new columns?

Question

I have a table which has a column which is a XML column. I can use this to get some values from the xml:

Select TOP 10 
    mq.Id
    , m.c.value('.', 'varchar(64)') as VisitId
    , n.c.value('.', 'varchar(64)') as VisitorId
    , p.c.value('.', 'varchar(64)') as SearchId
from [Message] as mq
    outer apply mq.ActivityLoggingData.nodes('ActivityLoggingData/VisitId') as m(c)
    outer apply mq.ActivityLoggingData.nodes('ActivityLoggingData/VisitorId') as n(c)
    outer apply mq.ActivityLoggingData.nodes('ActivityLoggingData/SearchId') as p(c)

The xml is similar to:

<ActivityLoggingData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <VisitId>c751de63-1305-4a5a-903e-677b5923d2c2</VisitId>
  <VisitorId>750b76eb-4727-4eb1-9737-cc7ce206e56b</VisitorId>
  <SearchId>baef33dc-8827-42a5-b2bd-a60ffe71aea8</SearchId>
  ...
</ActivityLoggingData>

Now, I added columns in table [Message]: VisitId, VisitorId and SearchId.

My question is: how can I extract these values from the xml column AND insert into these new columns? table Message has an Id column, we can use it and insert these values into temp table first, then update the [Message]. But, is there a way to do it without using temp table?

The database is SQL Server.

Thanks

Are you looking for this? update mq set VisitId = m.c.value('.', 'varchar(64)'), VisitorId = n.c.value('.', 'varchar(64)'), SearchId =p.c.value('.', 'varchar(64)') from [Message] as mq outer apply mq.ActivityLoggingData.nodes('ActivityLoggingData/VisitId') as m(c) outer apply mq.ActivityLoggingData.nodes('ActivityLoggingData/VisitorId') as n(c) outer apply mq.ActivityLoggingData.nodes('ActivityLoggingData/SearchId') as p(c); — Bhasyakarulu Kottakota
– Bhasyakarulu Kottakota, Commented May 8, 2020 at 2:16

ITHelpGuy · Accepted Answer · 2020-05-07 23:59:19Z

2

Please check this out.

DECLARE @xmldata XML = N'<ActivityLoggingData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <VisitId>c751de63-1305-4a5a-903e-677b5923d2c2</VisitId>
  <VisitorId>750b76eb-4727-4eb1-9737-cc7ce206e56b</VisitorId>
  <SearchId>baef33dc-8827-42a5-b2bd-a60ffe71aea8</SearchId>
</ActivityLoggingData>'

DECLARE @T TABLE(VisitId nvarchar(50), VisitorId nvarchar(50), SearchId nvarchar(50))

INSERT INTO @T(VisitId, VisitorId, SearchId)
SELECT x.value(N'(VisitId)[1]', N'nvarchar(50)'),
       x.value(N'(VisitorId)[1]', N'nvarchar(50)'),
       x.value(N'(SearchId)[1]', N'nvarchar(50)')
  FROM @xmldata.nodes(N'/ActivityLoggingData') AS XTbl(x)

SELECT * 
  FROM @T

answered May 7, 2020 at 23:59

ITHelpGuy

1,0472 gold badges19 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

marc_s Over a year ago

Since those really are GUIDs, one could also use the T-SQL datatype uniqueidentifier (instead of nvarchar(50)). But your solution definitely works just fine

urlreader Over a year ago

is there a way to just use ONE UPDATE to update the original table instead of using temp table/table variables to save the extracted value?

ITHelpGuy Over a year ago

I am not sure I understand your question. Could you please elaborate with an example.

Collectives™ on Stack Overflow

How to extract values from a XML column in SQL and insert into new columns?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related