Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting node values from an XML column Expand / Collapse
Author
Message
Posted Thursday, May 30, 2013 4:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 15, 2014 2:26 AM
Points: 188, Visits: 773
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-650DAEABB9C3	NULL	NULL	<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-650DAEABB9C3 NULL NULL <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-650DAEABB9C3 NULL NULL <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?
Post #1458107
Posted Thursday, May 30, 2013 4:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:45 AM
Points: 1,678, Visits: 19,552
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 (
.
.
.



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1458112
Posted Thursday, May 30, 2013 4:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 15, 2014 2:26 AM
Points: 188, Visits: 773
Many thanks! Works perfectly
Post #1458123
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse