Professional Web Applications Themes

Need help with openxml - Microsoft SQL / MS SQL Server

Hi we tried to load a xml file which have multiple same named elements (SubProductID) into a table. But we can only get the first row for each product (we tried all possible ways with OPENXML). How can we get all SubProductID for each product? Thanks for any ideas/suggestion/codes. FYI: If I put those SubProductID as attributes of SubProductID elements, I could get them all. See the next example (example 2). ================================================== ========================== ====== Example 1: DECLARE xml varchar(8000) DECLARE idoc int SELECT xml= '<ProductLinks> <ProductLink ProductID="1180"> <SubProductID>28059</SubProductID> <SubProductID>28057</SubProductID> <SubProductID>28058</SubProductID> </ProductLink> <ProductLink ProductID="1182"> <SubProductID>28057</SubProductID> <SubProductID>28058</SubProductID> </ProductLink> </ProductLinks> ' -- Create ...

  1. #1

    Default Need help with openxml

    Hi

    we tried to load a xml file which have multiple same named elements
    (SubProductID) into a table. But we can only get the first row for each
    product (we tried all possible ways with OPENXML). How can we get all
    SubProductID for each product? Thanks for any ideas/suggestion/codes.

    FYI: If I put those SubProductID as attributes of SubProductID elements, I
    could get them all. See the next example (example 2).



    ================================================== ==========================
    ======

    Example 1:

    DECLARE xml varchar(8000)

    DECLARE idoc int


    SELECT xml=

    '<ProductLinks>

    <ProductLink ProductID="1180">

    <SubProductID>28059</SubProductID>

    <SubProductID>28057</SubProductID>

    <SubProductID>28058</SubProductID>

    </ProductLink>

    <ProductLink ProductID="1182">

    <SubProductID>28057</SubProductID>

    <SubProductID>28058</SubProductID>

    </ProductLink>

    </ProductLinks>

    '

    -- Create an internal representation of the XML doent.

    EXEC sp_xml_preparedoent idoc OUTPUT, xml


    IF ERROR > 0

    BEGIN

    EXEC sp_xml_removedoent idoc

    -- RETURN -1

    END

    SELECT DISTINCT *

    FROM OPENXML (idoc, '/ProductLinks/ProductLink',3)

    WITH (

    ProductID int 'ProductID'

    , SubProductID int 'SubProductID'

    )

    EXEC sp_xml_removedoent idoc


    ProductID,SubProductID

    1180,28059

    1182,28057

    ================================================== ====================

    Example 2:

    DECLARE xml varchar(8000)

    DECLARE idoc int


    SELECT xml=

    '<ProductLinks>

    <ProductLink ProductID="1180">

    <SubProductID ProductID="28058"></SubProductID>

    <SubProductID ProductID="28057"></SubProductID>

    <SubProductID ProductID="28059"></SubProductID>

    </ProductLink>

    <ProductLink ProductID="1182">

    <SubProductID ProductID="28059"></SubProductID>

    <SubProductID ProductID="28057"></SubProductID>

    <SubProductID ProductID="28058"></SubProductID>

    </ProductLink>

    </ProductLinks>

    '

    -- Create an internal representation of the XML doent.

    EXEC sp_xml_preparedoent idoc OUTPUT, xml


    IF ERROR > 0

    BEGIN

    EXEC sp_xml_removedoent idoc

    -- RETURN -1

    END

    SELECT DISTINCT *

    FROM OPENXML (idoc, '/ProductLinks/ProductLink/SubProductID',3)

    WITH (

    ProductID int '../ProductID'

    , SubProductID int 'ProductID'

    )

    EXEC sp_xml_removedoent idoc


    ProductID,SubProductID

    1180,28058

    1180,28059

    1180,28057

    1182,28059

    1182,28058

    1182,28057

    What is wrong in the code?.Any help is appreciated.

    Thanks

    Meher


    Meher Guest

  2. #2

    Default Re: Need help with openxml

    This worked for me:

    DECLARE xml varchar(8000)

    DECLARE idoc int


    SELECT xml=

    '<ProductLinks>

    <ProductLink ProductID="1180">

    <SubProductID>28059</SubProductID>

    <SubProductID>28057</SubProductID>

    <SubProductID>28058</SubProductID>

    </ProductLink>

    <ProductLink ProductID="1182">

    <SubProductID>28057</SubProductID>

    <SubProductID>28058</SubProductID>

    </ProductLink>

    </ProductLinks>

    '

    -- Create an internal representation of the XML doent.

    EXEC sp_xml_preparedoent idoc OUTPUT, xml


    IF ERROR > 0

    BEGIN

    EXEC sp_xml_removedoent idoc

    -- RETURN -1

    END

    SELECT DISTINCT *

    FROM OPENXML (idoc, '/ProductLinks/ProductLink/SubProductID',3)

    WITH (

    ProductID int '../ProductID',

    SubProductID varchar(10) '.'

    )

    EXEC sp_xml_removedoent idoc




    Thanks,
    Roger Wolter

    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm.

    "Meher Malakapalli" <com> wrote in message
    news:phx.gbl... 
    ================================================== ========================== 


    SQL Guest

  3. #3

    Default Re: Need help with openxml

    Thank you very much Roger but the solution you gave us is not exactly what
    we are looking for. We tried your code and we get the following recordset
    back.

    ProductID,SubProductID

    1180,28059 28057 28058

    1182,28057 28058

    Here is What we want:

    ProductID,SubProductID

    1180,28058

    1180,28059

    1180,28057

    1182,28059

    1182,28058

    1182,28057

    Is that feasible?. please let us know. we truly appreciate the help you have
    done by going the extra mile to find a solution for our problem.

    Thanks

    Meher


    "SQL Server Development Team [MSFT]" <com> wrote in message
    news:%phx.gbl... 
    rights. [/ref]

    >[/ref]
    ================================================== ========================== 
    >
    >[/ref]


    Meher Guest

  4. #4

    Default Re: Need help with openxml

    I assume you did more than just run my code? When if just execute it in
    Query yzer, I get this:

    1182 28058

    1180 28059

    1182 28057

    1180 28058

    1180 28057

    (5 row(s) affected)




    --
    This posting is provided "AS IS" with no warranties, and confers no rights.


    "Meher Malakapalli" <com> wrote in message
    news:O#phx.gbl... 
    have 
    message 
    > rights. [/ref][/ref]
    each [/ref][/ref]
    elements, 
    > >[/ref]
    >[/ref]
    ================================================== ========================== 
    > >
    > >[/ref]
    >
    >[/ref]


    Roger Guest

  5. #5

    Default Re: Need help with openxml

    Thanks Roger. It was our short sightedness. we forgot to make a change at
    some other location. Thanks a lot. Your solution is what we were looking
    for.

    Thanks once again,

    Meher




    "Roger Wolter[MSFT]" <microsoft.com> wrote in message
    news:phx.gbl... 
    rights. [/ref]
    what [/ref]
    recordset 
    > have 
    > message 
    > > rights. [/ref]
    > each [/ref][/ref]
    all [/ref][/ref]
    ideas/suggestion/codes. [/ref]
    > elements, 
    > >[/ref]
    >[/ref]
    ================================================== ========================== [/ref][/ref]
    ================================================== ==================== 
    > >
    > >[/ref]
    >
    >[/ref]


    Meher Guest

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139