none
Strange XML results

    Domanda

  • When I issue this query:

           

    SELECT'PRODUCTION'AS[type]

            

    ,RECEIPT_ID AS[sscc]

    ,'BRU-etc'AS[location]

    ,MSG_CREATION_DATE AS[date]

    ,MSG_CREATION_TIME AS[time]

    ,'Zellik'AS[actorId]

    ,(SELECTTOP2 ASSET_GRAI AS[grai]

         

    FROMAPP_POOL_MGM_DELHAIZE_BE_ARCHIVE.dbo.Z_STG_BE_DELHAIZE_020_110 ASM_010_Det

    WHEREM_010_Det.RECEIPT_ID =M_010.RECEIPT_ID

      

    FORXMLPATH ('rti')


      

    )AS[load]

           

    FROMAPP_POOL_MGM_DELHAIZE_BE_ARCHIVE.dbo.Z_STG_BE_DELHAIZE_020_110 ASM_010

          

    WHEREM_010.REGISTRATION_DATE >='20180301'


      

    GROUPBYRECEIPT_ID

             

    ,MSG_CREATION_DATE

     

    ,MSG_CREATION_TIME

            

    FORXMLPATH ('transaction')

    I get strange XML results:

    <

    transaction>

      <

    type>PRODUCTION</type>

      <

    sscc>054250232028406748</sscc>

      <

    location>BRU-etc</location>

      <

    date>20180330</date>

      <

    time>090020</time>

      <

    actorId>Zellik</actorId>

      <

    load>&lt;rti&gt;&lt;grai&gt;54001110081270003325578&lt;/grai&gt;&lt;/rti&gt;&lt;rti&gt;&lt;grai&gt;54001110081270003390248&lt;/grai&gt;&lt;/rti&gt;</load>

    </

    transaction>

    For some reason in the detailed query I don't get < and > but &lt; and &gt;. When i run just the detailed query the results look fine.

    lunedì 2 luglio 2018 07:49

Risposte

  • try like this and it should work fine I guess

    cant test it as you didnt post any sample data

    SELECT 'PRODUCTION' AS [type]       
    ,RECEIPT_ID AS [sscc]
    ,'BRU-etc' AS [location]
    ,MSG_CREATION_DATE AS [date]
    ,MSG_CREATION_TIME AS [time]
    ,'Zellik'AS [actorId]
    ,(SELECT TOP2 ASSET_GRAI AS [grai]    
    FROM APP_POOL_MGM_DELHAIZE_BE_ARCHIVE.dbo.Z_STG_BE_DELHAIZE_020_110 ASM_010_Det
    WHERE M_010_Det.RECEIPT_ID =M_010.RECEIPT_ID 
    FOR XML PATH ('rti'),TYPE ) AS [load]
    FROM APP_POOL_MGM_DELHAIZE_BE_ARCHIVE.dbo.Z_STG_BE_DELHAIZE_020_110 ASM_010
    WHERE  M_010.REGISTRATION_DATE >='20180301'
    GROUP BY RECEIPT_ID
    ,MSG_CREATION_DATE
    ,MSG_CREATION_TIME
    FOR XML PATH ('transaction')


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Modificato Visakh16MVP lunedì 2 luglio 2018 08:26
    • Contrassegnato come risposta Alex van Dam giovedì 19 luglio 2018 07:26
    lunedì 2 luglio 2018 08:25
  • You've missed the TYPE in the inner query to preserve the XML data type:

    SELECT   'PRODUCTION' AS [type] ,
             RECEIPT_ID AS [sscc] ,
             'BRU-etc' AS [location] ,
             MSG_CREATION_DATE AS [date] ,
             MSG_CREATION_TIME AS [time] ,
             'Zellik' AS [actorId] ,
             (   SELECT TOP ( 2 ) ASSET_GRAI AS [grai]
                 FROM   APP_POOL_MGM_DELHAIZE_BE_ARCHIVE.dbo.Z_STG_BE_DELHAIZE_020_110 M_010_Det
                 WHERE  M_010_Det.RECEIPT_ID = M_010.RECEIPT_ID
                 FOR XML PATH('rti'), TYPE ) AS [load]
    FROM     APP_POOL_MGM_DELHAIZE_BE_ARCHIVE.dbo.Z_STG_BE_DELHAIZE_020_110 M_010
    WHERE    M_010.REGISTRATION_DATE >= '20180301'
    GROUP BY RECEIPT_ID ,
             MSG_CREATION_DATE ,
             MSG_CREATION_TIME
    FOR XML PATH('transaction');

    • Proposto come risposta HunchbackMVP lunedì 2 luglio 2018 12:11
    • Contrassegnato come risposta Alex van Dam giovedì 19 luglio 2018 07:26
    lunedì 2 luglio 2018 08:32

Tutte le risposte

  • try like this and it should work fine I guess

    cant test it as you didnt post any sample data

    SELECT 'PRODUCTION' AS [type]       
    ,RECEIPT_ID AS [sscc]
    ,'BRU-etc' AS [location]
    ,MSG_CREATION_DATE AS [date]
    ,MSG_CREATION_TIME AS [time]
    ,'Zellik'AS [actorId]
    ,(SELECT TOP2 ASSET_GRAI AS [grai]    
    FROM APP_POOL_MGM_DELHAIZE_BE_ARCHIVE.dbo.Z_STG_BE_DELHAIZE_020_110 ASM_010_Det
    WHERE M_010_Det.RECEIPT_ID =M_010.RECEIPT_ID 
    FOR XML PATH ('rti'),TYPE ) AS [load]
    FROM APP_POOL_MGM_DELHAIZE_BE_ARCHIVE.dbo.Z_STG_BE_DELHAIZE_020_110 ASM_010
    WHERE  M_010.REGISTRATION_DATE >='20180301'
    GROUP BY RECEIPT_ID
    ,MSG_CREATION_DATE
    ,MSG_CREATION_TIME
    FOR XML PATH ('transaction')


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Modificato Visakh16MVP lunedì 2 luglio 2018 08:26
    • Contrassegnato come risposta Alex van Dam giovedì 19 luglio 2018 07:26
    lunedì 2 luglio 2018 08:25
  • You've missed the TYPE in the inner query to preserve the XML data type:

    SELECT   'PRODUCTION' AS [type] ,
             RECEIPT_ID AS [sscc] ,
             'BRU-etc' AS [location] ,
             MSG_CREATION_DATE AS [date] ,
             MSG_CREATION_TIME AS [time] ,
             'Zellik' AS [actorId] ,
             (   SELECT TOP ( 2 ) ASSET_GRAI AS [grai]
                 FROM   APP_POOL_MGM_DELHAIZE_BE_ARCHIVE.dbo.Z_STG_BE_DELHAIZE_020_110 M_010_Det
                 WHERE  M_010_Det.RECEIPT_ID = M_010.RECEIPT_ID
                 FOR XML PATH('rti'), TYPE ) AS [load]
    FROM     APP_POOL_MGM_DELHAIZE_BE_ARCHIVE.dbo.Z_STG_BE_DELHAIZE_020_110 M_010
    WHERE    M_010.REGISTRATION_DATE >= '20180301'
    GROUP BY RECEIPT_ID ,
             MSG_CREATION_DATE ,
             MSG_CREATION_TIME
    FOR XML PATH('transaction');

    • Proposto come risposta HunchbackMVP lunedì 2 luglio 2018 12:11
    • Contrassegnato come risposta Alex van Dam giovedì 19 luglio 2018 07:26
    lunedì 2 luglio 2018 08:32