Loading XML data into table using SSIS

Question

I have a XML like below

<MYLIST>
        <Frame Id="89900981" Name="Gxown" Qty="0.0">
          <Frame Id="354653-01" Name="Frametop" Qty="1.0" />
          <Frame Id="656997-23" Name="FrameNM" Qty="1.0" />
          <Frame Id="776879921" Name="PINOBK" Qty="2.0" />
        </Frame>
</MYLIST>

Now I want to load the data into table like below

+----------+-----------+----------+-----+
| HEAD     | SUBS      | NAME     | QTY |
+----------+-----------+----------+-----+
| 89900981 | 354653-01 | Frametop | 1.0 |
+----------+-----------+----------+-----+
| 89900981 | 656997-23 | FrameNM  | 1.0 |
+----------+-----------+----------+-----+
| 89900981 | 776879921 | PINOBK   | 2.0 |
+----------+-----------+----------+-----+

Basically under the MYLIST node, the first node Frame is the Head of all. So I want to put that as in HEAD column (same for all rows under it) and other in SUBS column. Is this possible? I have tried using the output MYLIST_Frame_Frame. With this i'm able to load the SUBS,NAME and QTY column but the HEAD is available in MYLIST_Frame. I'm not sure how to combine this. I have tried using MERGE but that is inserting HEAD as a separate row.

Arwin Abdipour · Accepted Answer · 2020-11-11 13:18:47Z

1

you can use this code in your package: The core part of the answer is about using openxml function. It has three parameter in which the second one is in xpath format.

declare @Xml nvarchar(4000) = '
<MYLIST>
    <Frame Id="89900981" Name="Gxown" Qty="0.0">
      <Frame Id="354653-01" Name="Frametop" Qty="1.0" />
      <Frame Id="656997-23" Name="FrameNM" Qty="1.0" />
      <Frame Id="776879921" Name="PINOBK" Qty="2.0" />
    </Frame>

'

declare @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @Xml;
select
    (select
        Id as Head
     from openxml(@idoc, '/MYLIST/Frame', 1) 
     with (Id nvarchar(32))) as Head,
    Id as SUBS,
    [Name],
    Qty
from openxml(@idoc, '/MYLIST/Frame/Frame[position()<=3]', 1)
with 
    (Id nvarchar(32),
     [Name] nvarchar(32),
     [Qty] decimal(2,1))

edited Nov 11, 2020 at 13:18

answered Nov 11, 2020 at 12:03

Arwin Abdipour

113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pouria Hemi Over a year ago

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes.

Avinash Over a year ago

Thanks. But I'm looking for SSIS solution not a SQL code.

Collectives™ on Stack Overflow

Loading XML data into table using SSIS

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related