Getting node values from an XML column

• Chris Quinn-821458

  Hall of Fame

  Points: 3192

  More actions

  May 30, 2013 at 4:03 am

  #104199

  I have a table with a uniqueID column (context_id) and a column containing XML data in the format shown below:

  <?xml version="1.0" encoding="utf-8" ?>

  <KeyValues>

  <KeyValue>

  <Key>

  <ExternalKeyName>Deal code</ExternalKeyName>

  <InternalKeyName>Deal code</InternalKeyName>

  <KeyId>DEALCODE</KeyId>

  </Key>

  <KeyValueId>8e7230aa-ae15-4131-b4f4-c26b3da37979</KeyValueId>

  <Value>1160224</Value>

  </KeyValue>

  <KeyValue>

  <Key>

  <ExternalKeyName>Credit Agreement account number</ExternalKeyName>

  <InternalKeyName>Credit Agreement account number</InternalKeyName>

  <KeyId>CDTAGRNO</KeyId>

  </Key>

  <KeyValueId>db58108f-2d94-4e74-a891-923a58a4882d</KeyValueId>

  <Value>174552</Value>

  </KeyValue>

  <KeyValue>

  <Key>

  <ExternalKeyName>Ticketing Deadline </ExternalKeyName>

  <InternalKeyName>Ticketing Deadline Date</InternalKeyName>

  <KeyId>TICKTDLD</KeyId>

  </Key>

  <KeyValueId>03ee2376-8493-410a-b822-3dd94aa8a1b2</KeyValueId>

  <Value>2013-09-21 23:59:00Z</Value>

  </KeyValue>

  </KeyValues>

  I wish to query the KeyID and Value data into seperate rows, but am having probklems with the syntax

  I have the following query :

  SELECT

  context_id

  ,a.alias.value('@KeyId','Varchar(20)') AS 'KeyID'

  ,a.alias.value('@Value','Varchar(20)') AS 'Value'

  ,a.alias.query('.') AS 'Node'

  FROM (

  SELECT context_id

  ,cast(response_xml AS XML) as response_xml

  FROM dbo.tmpCMQ

  ) r

  CROSS APPLY r.response_xml.nodes('KeyValues/KeyValue') a(alias)

  Results:

  AE20D803-6367-4732-B625-650DAEABB9C3NULLNULL<KeyValue><Key><ExternalKeyName>Deal code</ExternalKeyName><InternalKeyName>Deal code</InternalKeyName><KeyId>DEALCODE</KeyId></Key><KeyValueId>8e7230aa-ae15-4131-b4f4-c26b3da37979</KeyValueId><Value>1160224</Value></KeyValue>

  AE20D803-6367-4732-B625-650DAEABB9C3NULLNULL<KeyValue><Key><ExternalKeyName>Credit Agreement account number</ExternalKeyName><InternalKeyName>Credit Agreement account number</InternalKeyName><KeyId>CDTAGRNO</KeyId></Key><KeyValueId>db58108f-2d94-4e74-a891-923a58a4882d</KeyValueId><Value>174552</Value></KeyValue>

  AE20D803-6367-4732-B625-650DAEABB9C3NULLNULL<KeyValue><Key><ExternalKeyName>Ticketing Deadline </ExternalKeyName><InternalKeyName>Ticketing Deadline Date</InternalKeyName><KeyId>TICKTDLD</KeyId></Key><KeyValueId>03ee2376-8493-410a-b822-3dd94aa8a1b2</KeyValueId><Value>2013-09-21 23:59:00Z</Value></KeyValue>

  Whatever I try, I can't get the values I want into KeyID and Value - I know it's probably a silly mistake in my syntax - can anyone help?

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  May 30, 2013 at 4:28 am

  #1619608

  SELECT

  context_id

  ,a.alias.value('(KeyValueId/text())[1]','Varchar(20)') AS 'KeyID'

  ,a.alias.value('(Value/text())[1]','Varchar(20)') AS 'Value'

  ,a.alias.query('.') AS 'Node'

  FROM (

  .

  .

  .

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Chris Quinn-821458

  Hall of Fame

  Points: 3192

  More actions

  May 30, 2013 at 4:51 am

  #1619617

  Many thanks! Works perfectly