|
View previous topic :: View next topic
|
| Author |
Message |
Arindam Ghosh
New User
Joined: 08 Feb 2014
Posts: 3
Location: India
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Arindam Ghosh
New User
Joined: 08 Feb 2014
Posts: 3
Location: India
|
|
|
|
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 |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
Arindam Ghosh
New User
Joined: 08 Feb 2014
Posts: 3
Location: India
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
|
|
