View previous topic :: View next topic
|
Author |
Message |
Gopalakrishnan V
Active User
Joined: 28 Jun 2010 Posts: 102 Location: chennai
|
|
|
|
Hi all,
I have a requirement to extract node name from XML via db2 dynamic query.I used the below code but it is not working :
select xmlcast(xmlquery('name(/*)' PASSING SRC_SYS_DATA_XML) as CHAR(40))
from F5980DBA.T_XML_STOR
where XML_STOR_ID = 21
XML payload:
<ns5:publishPlanResponse xmlns:ct="urn:complextypes.groupvoluntaryworkbenefits.product.ins.us.metlife.com" xmlns:cthdr="urn:complex.groupadmin.product.ins.us.metlife.com" xmlns:ns5="urn:metlife.com/us/ins/product/groupvoluntaryworkbenefits/PublishPlan_SyncServiceWSV1" xmlns:usbhdr="urn:messages.america.globaltechnology.metlife.com">
<cthdr:Header>
<usbhdr:MinorHeaderVersionNumber>0</usbhdr:MinorHeaderVersionNumber>
<usbhdr:MajorHeaderVersionNumber>1</usbhdr:MajorHeaderVersionNumber>
<usbhdr:UserID>
<usbhdr:UserID>tmahendran</usbhdr:UserID>
</usbhdr:UserID>
<usbhdr:MessageData>
<usbhdr:MessageCorrelation>
<usbhdr:TransactionCorrelationID>Q/4kQdOVn/tlZTVkZjEwOS0wNGRkLTQ3cmMAAAAA</usbhdr:TransactionCorrelationID>
</usbhdr:MessageCorrelation
(something like this)
I need to extract publishPlanResponse from this.
Can anyone help me with the correct code??
Thanks in advance |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
What "is not working" about this? Sample input, correct and incorrect sample output could illustrate the problem better.
(After so many years in the forum you should be able to use the code tags by now.) |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Quote: |
An XQuery expression starting with token "/" cannot be processed because the focus component of the dynamic context has not been assigned. |
meaning you haven't assigned on what the query should be run :
Code: |
select xmlcast(xmlquery('$doc/name(/*)' PASSING SRC_SYS_DATA_XML as "doc") as CHAR(40))
from F5980DBA.T_XML_STOR
where XML_STOR_ID = 21 |
or
Code: |
select xmlcast(xmlquery('$SRC_SYS_DATA_XML/name(/*)' PASSING SRC_SYS_DATA_XML as "SRC_SYS_DATA_XML ") as CHAR(40))
from F5980DBA.T_XML_STOR
where XML_STOR_ID = 21 |
|
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
this should also work :
Code: |
Select MyTable.MyRootName
from F5980DBA.T_XML_STOR
, XMLTABLE('$doc' passing SRC_SYS_DATA_XML as "doc"
COLUMNS
"MyRootName" char(40) PATH 'name(/*)') as MyTable
where XML_STOR_ID = 21
|
|
|
Back to top |
|
|
|