DB2 for z/os v10 NFM - tag names in xml include :

Shay Miller

DB2 for z/os v10 NFM - tag names in xml include :

Hello All.

 

We want to break down an XML document coming in from a distributed system into a standard DB2 table.

 

The problem is that the tag names contain " : " in the middle.

Xml sees this as "namespace:name" and thus an error message is generated for the PATH spec.

I can't do a change all to all of the occurrences of " : " because it's in the data itself too.

I didn't find a way around the problem.

 

Ideas will be gladly welcome.

 

The SQL statement (very basic example of the XML file):

 

INSERT INTO T1
SELECT X.* FROM
XMLTABLE ('$d/Sw:RMAFile/Sw:RMARecrd'
PASSING
XMLPARSE(DOCUMENT '
<Sw:RMAFile>
<Sw:RMARecrd>
<Sw:Tp>Received</Sw:Tp>
<Sw:RMASts>Enabled</Sw:RMASts>
<Doc:Issr>kkkkkk</Doc:Issr>
<Doc:Crspdt>ttttt</Doc:Crspdt>
<Doc:SvcNm>aaaa</Doc:SvcNm>
<Doc:IssdDtTm>2012-09-13T10:45:49Z</Doc:IssdDtTm>
</Sw:RMARecrd>
<Sw:RMAFile>
')
AS "d"
COLUMNS
SW_TP VARCHAR(30) PATH 'Sw:Tp',
SW_RMASTS VARCHAR(30) PATH 'Sw:RMASts',
DOC_ISSR VARCHAR(30) PATH 'Doc:Issr',
DOC_CRSPDT VARCHAR(30) PATH 'Doc:Crspdt',
DOC_SVCNM VARCHAR(30) PATH 'Doc:SvcNm',
DOC_ISSDDTTM VARCHAR(30) PATH 'Doc:IssdDtTm'
) AS X

 


 

DSNT408I SQLCODE = -16005, ERROR: AN XQUERY EXPRESSION REFERENCES AN ELEMENT
NAME, ATTRIBUTE NAME, TYPE NAME, FUNCTION NAME, NAMESPACE PREFIX, OR
VARIABLE NAME Sw THAT IS NOT DEFINED WITHIN THE STATIC CONTEXT. ERROR
QNAME= err:XPST0008

SQLERRM was Sw

 

Thanks,

Shay.

Lizette Koehler

DB2 for z/os v10 NFM - tag names in xml include :
(in response to Shay Miller)
How are you defining the data in DB2? Table definition or Column definition



Are there any schemes involved?



What are you using to load the data into DB2



What version of DB2 and z/OS are your running. Did you see if there is any maintenance for your level of DB2 for XML?





There is a redbook that might be helpful Extremely pureXML in DB2 10 for z/OS SG24-7915-00







Lizette





From: Shay Miller [mailto:[login to unmask email]
Sent: Thursday, July 27, 2017 4:18 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 for z/os v10 NFM - tag names in xml include :



Hello All.



We want to break down an XML document coming in from a distributed system into a standard DB2 table.



The problem is that the tag names contain " : " in the middle.

Xml sees this as "namespace:name" and thus an error message is generated for the PATH spec.

I can't do a change all to all of the occurrences of " : " because it's in the data itself too.

I didn't find a way around the problem.



Ideas will be gladly welcome.



The SQL statement (very basic example of the XML file):



INSERT INTO T1
SELECT X.* FROM
XMLTABLE ('$d/Sw:RMAFile/Sw:RMARecrd'
PASSING
XMLPARSE(DOCUMENT '


Received
Enabled
kkkkkk
ttttt
aaaa
2012-09-13T10:45:49Z


')
AS "d"
COLUMNS
SW_TP VARCHAR(30) PATH 'Sw:Tp',
SW_RMASTS VARCHAR(30) PATH 'Sw:RMASts',
DOC_ISSR VARCHAR(30) PATH 'Doc:Issr',
DOC_CRSPDT VARCHAR(30) PATH 'Doc:Crspdt',
DOC_SVCNM VARCHAR(30) PATH 'Doc:SvcNm',
DOC_ISSDDTTM VARCHAR(30) PATH 'Doc:IssdDtTm'
) AS X



_____



DSNT408I SQLCODE = -16005, ERROR: AN XQUERY EXPRESSION REFERENCES AN ELEMENT
NAME, ATTRIBUTE NAME, TYPE NAME, FUNCTION NAME, NAMESPACE PREFIX, OR
VARIABLE NAME Sw THAT IS NOT DEFINED WITHIN THE STATIC CONTEXT. ERROR
QNAME= err:XPST0008

SQLERRM was Sw



Thanks,

Shay.

Shay Miller

RE: DB2 for z/os v10 NFM - tag names in xml include :
(in response to Lizette Koehler)

Hello Lizette.

 

Thank you for your answer.

 

The DDL of the table:

CREATE TABLE
T1
(
SW_TP CHAR(30) NOT NULL
,SW_RMASTS CHAR(30) NOT NULL
,DOC_ISSR CHAR(30) NOT NULL
,DOC_CRSPDT CHAR(30) NOT NULL
,DOC_SVCNM CHAR(30) NOT NULL
,DOC_ISSDDTTM CHAR(30)
)
IN DB1.TS1
DATA CAPTURE CHANGES
CCSID EBCDIC
NOT VOLATILE
APPEND NO
;

 

No schemes involved.

 

I'm trying to insert the data into the table using the XMLTABLE function. this is the problematic SQL.

 

DB2 V10 nfm , z/OS 2.2. I didn't find XML maintenance that I don't  have.

 

I've looked in the redbook and didn't find a solution.

 

To be more specific in my question , how do i get DB2 to understand that "Sw" is not a namespace but part of the tag name?

 

Thanks,

Shay.
In Reply to Lizette Koehler:

How are you defining the data in DB2? Table definition or Column definition



Are there any schemes involved?



What are you using to load the data into DB2



What version of DB2 and z/OS are your running. Did you see if there is any maintenance for your level of DB2 for XML?





There is a redbook that might be helpful Extremely pureXML in DB2 10 for z/OS SG24-7915-00







Lizette





From: Shay Miller [mailto:[login to unmask email]
Sent: Thursday, July 27, 2017 4:18 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 for z/os v10 NFM - tag names in xml include :



Hello All.



We want to break down an XML document coming in from a distributed system into a standard DB2 table.



The problem is that the tag names contain " : " in the middle.

Xml sees this as "namespace:name" and thus an error message is generated for the PATH spec.

I can't do a change all to all of the occurrences of " : " because it's in the data itself too.

I didn't find a way around the problem.



Ideas will be gladly welcome.



The SQL statement (very basic example of the XML file):



INSERT INTO T1
SELECT X.* FROM
XMLTABLE ('$d/Sw:RMAFile/Sw:RMARecrd'
PASSING
XMLPARSE(DOCUMENT '


Received
Enabled
kkkkkk
ttttt
aaaa
2012-09-13T10:45:49Z


')
AS "d"
COLUMNS
SW_TP VARCHAR(30) PATH 'Sw:Tp',
SW_RMASTS VARCHAR(30) PATH 'Sw:RMASts',
DOC_ISSR VARCHAR(30) PATH 'Doc:Issr',
DOC_CRSPDT VARCHAR(30) PATH 'Doc:Crspdt',
DOC_SVCNM VARCHAR(30) PATH 'Doc:SvcNm',
DOC_ISSDDTTM VARCHAR(30) PATH 'Doc:IssdDtTm'
) AS X



_____



DSNT408I SQLCODE = -16005, ERROR: AN XQUERY EXPRESSION REFERENCES AN ELEMENT
NAME, ATTRIBUTE NAME, TYPE NAME, FUNCTION NAME, NAMESPACE PREFIX, OR
VARIABLE NAME Sw THAT IS NOT DEFINED WITHIN THE STATIC CONTEXT. ERROR
QNAME= err:XPST0008

SQLERRM was Sw



Thanks,

Shay.

J&#248;rn Thyssen

RE: DB2 for z/os v10 NFM - tag names in xml include :
(in response to Shay Miller)

Hi Shay,

Have you read this article? https://www.ibm.com/developerworks/data/library/techarticle/dm-0611saracco/index.html

Case 9 is a XMLTABLE example

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Shay Miller

RE: DB2 for z/os v10 NFM - tag names in xml include :
(in response to Jørn Thyssen)

Thank you Jørn.

 

Using the explanations and examples in the article, I changed the command and it works great.

 

What was missing were namespaces declarations in the path statements of the XMLTABLE command.


In Reply to Jørn Thyssen:

Hi Shay,

Have you read this article? https://www.ibm.com/developerworks/data/library/techarticle/dm-0611saracco/index.html

Case 9 is a XMLTABLE example

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Edited By:
Shay Miller[Organization Members] @ Jul 31, 2017 - 02:26 PM (Asia/Jerusalem)