Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    10

    Unanswered: XML - missing node when NULL

    Hello
    I'm digging google for the last few days and stuck without sulution.

    Main problem is, when I construct XML content from directly from SQL, there are no empty XML nodes generated for null values.

    Example

    Code:
    SELECT XMLELEMENT 
    		(
    			name root_name, 
    			XMLFOREST
    			(
    				id_msg as ID, 
    				datepar AS Date,
    				xml_subnode as aaa
    			        (
    					SELECT XMLAGG(XMLFOREST 
    					(
    						subid AS subid
    					))
    				
    					FROM sub_table
    					WHERE r_datum=datepar
    				) AS Subtree
    			)
    		)
    	INTO xml_node;
    When SELECT XMLAGG... returns no items, no Subtree node is placed into XML_node. It should be represented as '<Subtree />' but the is none.

    The same about simply values. If datepar is NULL, no Date XML item will be present in final XML document.

    I found I can build XML ducument using XMLELEMENT in iteration loop concatenating result elements - XMLELEMENT for single field returns nulls in correct way. But how to do that SELECTING data into SQL?

    EDIT: Further investigation shown that XMLFOREST function makes null elements missing.

    please help

    Best regards
    Last edited by MaXyM; 04-12-10 at 09:56.

  2. #2
    Join Date
    Apr 2010
    Posts
    10
    I decided to put better explanation of the problem using complete example

    Code:
    CREATE TABLE t1
    (
    id1 INT
    fielda DATE
    )
    
    CREATE TABLE t2
    (
    id2 INT,
    id1 INT
    )
    I want to get XML like this

    Code:
    <xml>
    <id1>...</id1>
    <fielda>...</fielda>
    <sub>
            <id2>...</id2>
            <id2>...</id2>
            <id2>...</id2>
            <id2>...</id2>
    </sub>
    </xml>
    where SUB contains SELECT id2 FROM t2 WHERE t2.id2=t1.id2

    Code:
    DECLARE
    xmldoc XML;
    
    SELECT XMLELEMENT 
    		(
    			name xml, 
    			XMLFOREST
    			(
    				id1 as id1, 
    				fielda as fielda
    			        (
    					SELECT XMLAGG(XMLFOREST 
    					(
    						id2 AS id2
    					))
    				
    					FROM t2
    					WHERE t2.id2=t1.id2
    				) AS Sub
    			)
    		)
    	INTO xmldoc
    	FROM t1
    	WHERE id1=...
    Problem is when there is no related records in t2 for given t1.id2. In that case there will be no <sub /> tag. Sub node will be missing since SELECT FROM t2 will return no records.



    Another examples which are related to the problem:
    Code:
    SELECT XMLELEMENT (name some_field, null);
    -- works ok, returns <some_field />


    Code:
     SELECT XMLFOREST (
    	1 as some_field1, 
    	null as some_field_null)
    doesn't work as expected. Returns only <some_field1>1</some_field1>
    Last edited by MaXyM; 04-12-10 at 11:16.

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    I don't have time to look it up. But I think that is what the spec calls for. Oracle behaves the same way.

    Code:
    SELECT XMLFOREST(1 AS some_field, 
       COALESCE(possibly_null_field, '') AS another_field)

  4. #4
    Join Date
    Apr 2010
    Posts
    10
    Thanx.
    It will work for single value. But what about sub-select?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •