Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Facing Error while inserting data into XML column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Arindam Ghosh

New User


Joined: 08 Feb 2014
Posts: 3
Location: India

PostPosted: Fri Feb 14, 2014 1:54 pm    Post subject: Facing Error while inserting data into XML column
Reply with quote

I am working on DB2 V10.I have created one new table which has one XML column(Data type is XML).
For this XML column DB2 have created one internal table with below properties:-
(
--DOCID BIGINT
,--MIN_NODEID VARBINARY(128)-- NOT NULL
,--XMLDATA VARBINARY(15850)-- NOT NULL
)

Now we are using one COBOL program to read data from a sequential file and insert into this XML column.The input file is of record length 600.

Now the problem we are facing is that if the input file is limited to 29 lines(i.e 29*600 = 17400) the insert is executing successfully.But if the input file crosses 29 lines the insert is abending (SQLCODE : 000002034N,SQLSTATE : 2200L).The format of the XML file is perfect.The XML tags everything are in proper place.

It looks like after char 17400 it is rejecting the rest of data for XML column and that’s why it is not checking the closing tags and throwing error as invalid XML format.

Can anyone please guide me about what approach I can take to resolve this.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Feb 14, 2014 8:11 pm    Post subject:
Reply with quote

Hello and welcome to the forums,

It sounds like you are trying to pour 6 litres of "stuff" into a 5 litre bucket.

If i'm understanding correctly, you might pour less stuff or create a bigger bucket.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Fri Feb 14, 2014 10:20 pm    Post subject:
Reply with quote

Dick,

Quote:
6 litres of "stuff" into a 5 litre bucket


The phrase I have heard:
1) Is not metric (uses lbs)
2) Uses a Bag
3) Uses Fragrant 'stuff'
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Feb 14, 2014 11:04 pm    Post subject:
Reply with quote

Hi Dave,

Yup, we've been some of the same places . . .

Hopefully, TS will follow up if there is still something that is not clear.

d
Back to top
View user's profile Send private message
Arindam Ghosh

New User


Joined: 08 Feb 2014
Posts: 3
Location: India

PostPosted: Sat Feb 15, 2014 8:32 pm    Post subject: Reply to: Facing Error while inserting data into XML column
Reply with quote

Hi Dick,

I agree with your comment 'It sounds like you are trying to pour 6
litres of "stuff" into a 5 litre bucket.' and I also feel the same.
Thats why I have explained below about 17400.

But the main problem here is that for XML datatype column DB2
automatically created the internal table with below properties.I have
not specified anything here.
(
--DOCID BIGINT
,--MIN_NODEID VARBINARY(128)-- NOT NULL
,--XMLDATA VARBINARY(15850)-- NOT NULL
)

I have just created the main table as below.
CREATE TABLE XML_DATA_TABLE
(
XML_DATA_ID CHAR(4) NOT NULL,
XML_TEXT XML,
XML_DATA_IND CHAR(1)
)
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Sat Feb 15, 2014 11:11 pm    Post subject:
Reply with quote

may be u can look at XML limits in DB2 v10 Manual.

Maximum length of XML pattern text 4000 bytes after conversion to UTF-8
Maximum length of an XML element or attribute name in an XML document 1000 bytes
Maximum length of a namespace uri 1000 bytes
Maximum length of a namespace prefix 998 bytes
Largest depth of an internal XML tree 128 levels
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Feb 17, 2014 2:33 pm    Post subject:
Reply with quote

Hi Arindam,

Can you show us your INSERT statement.

Only limit an XML column has is the column value can be up to maximum of only 2GB. Thats why you don't specify the size while defining the table.

Have a look at SQLSTATE : 2200L

Quote:
SQL20345N
The XML value is not a well-formed document with a single root element.

Explanation
The XML value is not a well-formed document. An XML value that is being stored in a table or validated must be a well-formed XML document with a single root element. The document node of the XML value must not have any text node children, although it can have comment node or processing instruction node children.

The statement cannot be processed.

User response
Storing or validating the XML value requires that the XML value be modified so that it is a well-formed document with a single root element. If the document is well-formed, ensure that the document node of the XML value has only a single element node as a child and no text node child. Otherwise, do not attempt store or validate the XML value.

sqlcode: -20345

sqlstate: 2200L

Also take a look at this link, as it states DB2 is looking for a Document Node, a standard that XQuery and DB2 pureXML built on. Probably you should try something like
Code:
insert into foo values(4,xmldocument(xmlquery('<test>hello</test>')));
insert into foo values(2,xmldocument(xmlelement(name "test",'hello')));

Thanks,
Sushanth
Back to top
View user's profile Send private message
Arindam Ghosh

New User


Joined: 08 Feb 2014
Posts: 3
Location: India

PostPosted: Mon Feb 17, 2014 10:01 pm    Post subject: Reply to: Facing Error while inserting data into XML column
Reply with quote

Thanks Sushanth for your reply. Currently I am using the below query in Cobol program.

INSERT
INTO XML_DATA_TABLE
(
XML_DATA_ID
,XML_TEXT
,XML_DATA_IND
)
VALUES
(
:XML-DATA-ID
,:XML-TEXT-DATA
,:XML-DATA-IND
)


Here in the host variable XML-TEXT-DATA I am moving the data from input file. The input file is of record length 600. The input file's xml format is also correct.As I already explained above I am getting 2200L error only when the input file exceeds 29 lines(i.e 29*600 = 17400).If I kept it within 29 lines the insert is executing successfully and record getting loaded.

As the input file is in proper xml format,so I dont think I have to use xmlquery/xmlelement functions.Main problem happening here is the size.

Let me know your thought in this.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Feb 18, 2014 12:25 pm    Post subject:
Reply with quote

Hi Arindam,

The error says "The XML value is not a well-formed document with a single root element."

So try using the XMLDOCUMENT function and let us know.

17400 bytes is still less than 2GB. Try above option.

Thanks,
Sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us