Post reply

XML Explicit Error

  • July 13, 2012 at 6:24 am

    #98553

    Hi,

    Can anyone please let me know where I am Goin wrong with this code.

    I am getting the following error

    Msg 6833, Level 16, State 1, Line 3 Parent tag ID 2 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

    All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

    And I made sure all the select statements return equal num of columns

    SELECT 1 AS Tag,

    NULL AS Parent,

    --NULL AS [Message!1!' '],

    'IDC'AS [Message!1!SOURCE],

    'ILO0008' AS [Message!1!Target],

    'inCreateInvXaction' AS [Message!1!Type],

    i.effectivedate as [Message!1!date!Hide] ,

    --NULL AS [InventoryTransaction!2!' '],

    NULL AS [InventoryTransaction!2!transaction_code],

    NULL AS [InventoryTransaction!2!transaction_quantity],

    NULL AS [InventoryTransaction!2!allow_partial],

    NULL AS [InventoryTransaction!2!create_item_warehouse],

    NULL AS [InventoryTransaction!2!create_item_location],

    NULL AS [InventoryTransaction!2!entered_by_user],

    --NULL AS [Transaction!3!' '],

    NULL AS [Transaction!3!company],

    NULL AS [Transaction!3!item_number],

    NULL AS [Transaction!3!warehouse],

    NULL AS [Transaction!3!location]

    FROM PRD1.WH2.ITRN i

    WHERE i.storerkey = 'OCU'

    AND i.trantype <> 'MV'

    AND (i.trantype <> 'WD' OR i.trantype = 'WD' AND i.sourcetype = 'ntrTransferDetailAdd')

    UNION ALL

    SELECT 2 AS Tag,

    1 AS Parent,'IDC',NULL,NULL,i.effectivedate,

    CASE

    WHEN i.trantype ='DP' THEN 'D'

    WHEN i.trantype ='AJ' THEN 'A'

    WHEN i.trantype ='WD' THEN 'W'

    END AS 'transaction_code' ,i.qty,'Y', 'N', 'N','ADMIN' ,NULL, NULL

    ,NULL,NULL

    FROM PRD1.WH2.ITRN i

    WHERE i.storerkey = 'OCU'

    AND i.trantype <> 'MV'

    AND (i.trantype <> 'WD' OR i.trantype = 'WD' AND i.sourcetype = 'ntrTransferDetailAdd')

    and i.itrnkey=0009354944

    --ORDER BY i.EffectiveDate

    Union all

    SELECT 3 AS Tag,

    2 AS Parent,'IDC',NULL,NULL,i.effectivedate ,NULL , NULL,NULL, NULL,NULL,NULL,NULL,'101',Ltrim(Rtrim(i.Sku)) as 'item_number',1,'M010101'

    FROM PRD1.WH2.ITRN i

    WHERE i.storerkey = 'OCU'

    AND i.trantype <> 'MV'

    AND (i.trantype <> 'WD' OR i.trantype = 'WD' AND i.sourcetype = 'ntrTransferDetailAdd')

    and i.itrnkey=0009354944

    ORDER BY [Message!1!date!Hide]

    --,[Transaction!3!company], [InventoryTransaction!2!transaction_code],[Message!1!SOURCE]

    FOR XML EXPLICIT

  • July 13, 2012 at 6:32 am

    #1512496

    Additional Information:

    I want to see my XML in the following format:

    <![CDATA[

    <Message source="IDC" target="ILO0008" type="inCreateInvXaction">

    <InventoryTransaction transaction_code="A" transaction_quantity="999999" allow_partial="Y"

    create_item_warehouse="N" create_item_location="N" entered_by_user="ADMIN">

    <Transaction company="101" item_number="ENROLL" warehouse="1" location="M010101" />

    </InventoryTransaction>

    </Message>

  • July 13, 2012 at 9:20 am

    #1512613

    I think the problem is with the order by clause. Try changing this to order by Tag first and see if that helps.

  • July 13, 2012 at 9:45 am

    #1512630

    I already tried using

    Order By [Transaction!3!company], [InventoryTransaction!2!transaction_code],[Message!1!SOURCE]

    or I as well Tried with Order By 1,2,3

    But that didn't help 🙁

  • July 16, 2012 at 6:36 am

    #1513026

    i think you must try again to start XML EXPLICIT

  • July 17, 2012 at 1:25 am

    #1513431

    What's the meaning of

    FOR XML EXPLICIT

    ?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply