xml read file and write to xml file

Question

I have xml data in below format. I want to convert this to proper xml format where I can use data flow task -> xml source component to import data into DB. Please help me out what should be done?

<OrderWithProfile Ver="1.0" System="abc" Profile="test">
<Users ID="aaa">
</Users><Users ID="bbb">
</Users><Users ID="ccc"></Users></OrderWithProfile>

score 0 · Answer 1 · answered Jun 17 '19 at 14:36

The given xml format is correct, you can check it by saving the xml in notepad and save it as xml.Then open the file with internet explorer you will get the xml tag if there is no error in the file.

If you are using DFT XML source component then you can give the path for the xml file and generate the xsd on the same location. If you are using variable then give the specific variable name.

Create the table with column to store ID with datatype as nvarchar(50) and use this table in OLEDB destination.

To process the xml using sql in Execute sql task

 CREATE TABLE [dbo].[ID_detail](
    [ID] [nvarchar](255) NULL
) ON [PRIMARY]

GO  

DECLARE @x xml = '<OrderWithProfile Ver="1.0" System="abc" Profile="test">
    <Users ID="aaa"></Users>
    <Users ID="bbb"></Users>
    <Users ID="ccc"></Users>
    </OrderWithProfile>'

    INSERT into ID_detail
    SELECT a.b.value('./@ID','varchar(100)') AS ID
    FROM @x.nodes('/OrderWithProfile/Users') as a(b)

xml read file and write to xml file

1 Answers1