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="Paul.Walker@demoexample.com"/>
    <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

Check if this could help you https://stackoverflow.com/questions/23096527/how-to-convert-xml-data-into-row-column-data-in-sql-server#answer-23097475 — Sowmyadhar Gourishetty, Aug 04 '20 at 19:01
"clob" is an oracle datatype, correct? If so, why tag of SQL Server? — SMor, Aug 04 '20 at 20:33
@SMor yes its nvarchar(max) . for general understanding i said it clob. — BRB, Aug 05 '20 at 16:56
@DaleK i tried whatever i could find here. but count find any parser which was for XML format which i have. — BRB, Aug 05 '20 at 16:57

score 0 · Answer 1 · answered Aug 11 '20 at 13:18

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="Paul.Walker@demoexample.com"/>
    <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.

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

1 Answers1