Read key Value pair XML from nvarchar in query which returns value in columns

Question

I want to read an XML nvarchar column:

<Attributes>
  <Map>
    <entry key="costcenter">
      <value>
        <List>
          <String>R03e</String>
          <String>L07e</String>
        </List>
      </value>
    </entry>
    <entry key="department" value="Information Technology"/>
    <entry key="email" value="[email protected]"/>
    <entry key="employeeId" value="1a2a3b4d"/>
    <entry key="firstName" value="Paul"/>
    <entry key="fullName" value="Paul.Walker"/>
    <entry key="inactiveIdentity" value="FALSE"/>
    <entry key="lastName" value="Walker"/>
    <entry key="location" value="Austin"/>
    <entry key="managerId" value="1a2c3a4d"/>
    <entry key="region" value="Americas"/>
  </Map>
</Attributes>

I want results in columns like this:

costSenter | department | email | employeeId | firstName | lastName | location | managerId | region

Answer 1

You can use nodes xml method (more info here) to shred your xml, apply attribute filtering and extract the information from value attribute.

You can use cross apply to extend this operation to all the <entry> tags

Here is a basic query that you can use as a starting point:

declare @src nvarchar(max)='
<Attributes>
  <Map>
    <entry key="costcenter">
      <value>
        <List>
          <String>R03e</String>
          <String>L07e</String>
        </List>
      </value>
    </entry>
    <entry key="department" value="Information Technology"/>
    <entry key="email" value="[email protected]"/>
    <entry key="employeeId" value="1a2a3b4d"/>
    <entry key="firstName" value="Paul"/>
    <entry key="fullName" value="Paul.Walker"/>
    <entry key="inactiveIdentity" value="FALSE"/>
    <entry key="lastName" value="Walker"/>
    <entry key="location" value="Austin"/>
    <entry key="managerId" value="1a2c3a4d"/>
    <entry key="region" value="Americas"/>
  </Map>
</Attributes>'

;with x as
(
    select  cast(@src as xml) as attributes
)
select 
      costcenter.s.value('.', 'nvarchar(max)') as costcenter
    , department.s.value('.', 'nvarchar(max)') as department
    , email.s.value('.', 'nvarchar(max)') as email
    , employeeId.s.value('.', 'nvarchar(max)') as employeeId
    , firstName.s.value('.', 'nvarchar(max)') as firstName
    , fullName.s.value('.', 'nvarchar(max)') as fullName
    , inactiveIdentity.s.value('.', 'nvarchar(max)') as inactiveIdentity
    , lastName.s.value('.', 'nvarchar(max)') as lastName
    , loc.s.value('.', 'nvarchar(max)') as [location]
    , managerID.s.value('.', 'nvarchar(max)') as managerId
    , region.s.value('.', 'nvarchar(max)') as region
 
from x
    CROSS APPLY attributes.nodes('//entry[@key = "costcenter"]/value') costcenter(s)
    CROSS APPLY attributes.nodes('//entry[@key = "department"]/@value') department(s)
    CROSS APPLY attributes.nodes('//entry[@key = "email"]/@value') email(s)
    CROSS APPLY attributes.nodes('//entry[@key = "employeeId"]/@value') employeeId(s)
    CROSS APPLY attributes.nodes('//entry[@key = "firstName"]/@value') firstName(s)
    CROSS APPLY attributes.nodes('//entry[@key = "fullName"]/@value') fullName(s)
    CROSS APPLY attributes.nodes('//entry[@key = "inactiveIdentity"]/@value') inactiveIdentity(s)
    CROSS APPLY attributes.nodes('//entry[@key = "lastName"]/@value') lastName(s)
    CROSS APPLY attributes.nodes('//entry[@key = "location"]/@value') loc(s)
    CROSS APPLY attributes.nodes('//entry[@key = "managerId"]/@value') managerID(s)
    CROSS APPLY attributes.nodes('//entry[@key = "region"]/@value') region(s)

Results:

P.S. In the future please follow the advice you got in the comments: post the code you have tried so far highlighting the problems you were unable to solve.