FOR XML ... RAW [ [WITH | USE] CDATA ]

[Ivan Smahin]

Hello william,

Tuesday, November 4, 2008, 5:47:54 PM, you wrote:

> Thanks --

> I also found out something else.

> This SQL:

> select i_billoflading_number, Consignee.clients, Shipper.clients
> from 
> info_billoflading join info_clients as Consignee on consignees
> join info_clients as Shipper on shippers
> where i_billoflading_voyagenumber_ref = 'SS504'
> order by i_billoflading_number for xml auto element 

> Results in this XML:

> "<info_billoflading>
>  <i_billoflading_number><![CDATA[001]]></i_billoflading_number>
>  <Consignee>
>   <clients><![CDATA[International Motors LTD]]></clients>
>   <Shipper>
>    <clients><![CDATA[International Motors Ltd]]></clients>
>   </Shipper>
>  </Consignee>
> </info_billoflading>
> <info_billoflading>
>  <i_billoflading_number><![CDATA[002]]></i_billoflading_number>
>  <Consignee>
>   <clients><![CDATA[Nite Oil LTD]]></clients>
>   <Shipper>
>    <clients><![CDATA[PETROLEUM, TRAMMO ]]></clients>
>   </Shipper>
>  </Consignee>
> </info_billoflading>
> <info_billoflading>
>  <i_billoflading_number><![CDATA[004]]></i_billoflading_number>
>  <Consignee>
>   <clients><![CDATA[Hawk Enterprises]]></clients>
>   <Shipper>
>    <clients><![CDATA[BVI Gas Inc.]]></clients>
>   </Shipper>
>  </Consignee>
> </info_billoflading>
> <info_billoflading>

> What I was expecting is:

> <info_billoflading>
>          
> <i_billoflading_number><![CDATA[004]]></i_billoflading_number>
>  
>   <Consignee><![CDATA[Hawk Enterprises]]> </Consignee>
>   <Shipper><![CDATA[BVI Gas Inc.]]></Shipper>

> </info_billoflading>


Consignee and Shipper are both the tables - right?
info_billoflading is table too.

You think it should be
<info_billoflading>
    <i_billoflading_number>...</i_billoflading_number>
</info_billoflading>

but
<Consignee>...</Consignee>

Why?

There is just a tree-view of join result:
<t1>
      <t1_f1>...</t1_f1>
      <t1_f2>...</t1_f2>

      <t2>
         <t2_f1>...</t2_f1>
      </t2>

      ....
</t1>


Each record from t1 joined to some records of t2 and so on...


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com