CODEWITHSUNDEEP
sql-serverxmlt-sqlxpathxquery-sql
Electrionics
Electrionics

Reputation: 6772

T-sql data migration from xml to columns

I have temp table #xml that have 2 columns:

CREATE TABLE #xml
(
    id int,
    xmlroutes xml
)
INSERT INTO #xml (id, xmlroutes)
(SELECT TOP 50 Id, CAST(RouteParameters as xml) as xmlroutes FROM LoggingRecords WHERE RouteParameters IS NOT NULL)

I have selected id and pair xml with next script:

(SELECT id, T2.Loc.query('.') as pair
FROM   #xml
CROSS APPLY xmlroutes.nodes('/route/pair') as T2(Loc))

LoggingRecords contains string (nvarchar) that castable to xml.

I have next xml structure of all records:

<route>
   <pair key="x" value="y"/>
   <pair key="z" value="1"/>
   <pair key="a" value="b"/>
</route>

I want to select next data from each record of #xml table (each id can relate with many pairs):

  1. Id
  2. key
  3. value

How can I do this?

Upvotes: 1

Views: 583

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

select X.id,
       T.N.value('@key', 'nvarchar(50)') as [Key],
       T.N.value('@value', 'nvarchar(50)') as [Value]
from #xml as X
  cross apply X.xmlroutes.nodes('/route/pair') as T(N)

SE-Data

Upvotes: 2

Related Questions