SQL Server SSIS - how can I split an XML record into multiple records with different values for each column?

w8rqjzmb 于 2023-05-28 发布在其他

关注(0)|答案(2)|浏览(125)

I have the following XML Source File:

<documents>
<document fileNo='1234'>
<item name='EmployerEmail'><text>BarryBlue@Blue.Inc</text></item>
<item name='EmployerForename'><text>Barry</text></item>
<item name='EmployerSurname'><text>Blue</text></item>
<item name='EmployeeEmail'><text>Gary</text></item>
<item name='EmployeeForename'><text>Green</text></item>
<item name='EmployeeSurname'><text>GarryGreen@Blue.Inc</text></item>
</document>

I want to export this record into an excel file as two separate records for the employer and employee. So my end result would look like this:

I have gotten as far as being able to export the above as a single record with 8 columns. But I'm not sure how to separate them in to two record of 5 columns each.

I am using dataflows in SSIS so if it can be done with dataflows that would be ideal. But if code is the only way I can work with that.

sql-server

来源：https://stackoverflow.com/questions/76315839/ssis-how-can-i-split-an-xml-record-into-multiple-records-with-different-values

2条答案

按热度按时间

w1jd8yoj1#

Just in case, T-SQL and XQuery based solution.

SQL

DECLARE @xml XML =
    N'<documents>
        <document fileNo="1234">
            <item name="EmployerEmail">
                <text>BarryBlue@Blue.Inc</text>
            </item>
            <item name="EmployerForename">
                <text>Barry</text>
            </item>
            <item name="EmployerSurname">
                <text>Blue</text>
            </item>
            <item name="EmployeeEmail">
                <text>GarryGreen@Blue.Inc</text>
            </item>
            <item name="EmployeeForename">
                <text>Gary</text>
            </item>
            <item name="EmployeeSurname">
                <text>Green</text>
            </item>
        </document>
    </documents>';
    
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT @@SPID)) AS Nmbr 
       , @xml.value('(/documents/document/@fileNo)[1]','INT') AS fileNo
       , LEFT(c.value('@name', 'VARCHAR(30)'), 8) AS Type
       , c.value('(/documents/document/*[sql:column("seq.pos")]/text/text())[1]', 'VARCHAR(30)') AS Email
       , c.value('(/documents/document/*[sql:column("seq.pos") + 1]/text/text())[1]', 'VARCHAR(30)') AS FirstName
       , c.value('(/documents/document/*[sql:column("seq.pos") + 2]/text/text())[1]', 'VARCHAR(30)') AS LastName
       , seq.pos   -- just to see
    FROM @xml.nodes('/documents/document/*[position() mod 3 = 1]') AS t(c)
       CROSS APPLY (SELECT t.c.value('let $n := . return count(/documents/document/*[. << $n[1]]) + 1','INT') AS pos
             ) AS seq;

Output

Nmbr	fileNo	Type	Email	FirstName	LastName	pos
1	1234	Employer	BarryBlue@Blue.Inc	Barry	Blue	1
2	1234	Employee	GarryGreen@Blue.Inc	Gary	Green	4

赞(0）回复(0）举报 2023-05-28

q3aa05252#

Resolved myself.

I added a multicast to two pivot tables, then used Union All to combine them and outputted to an excel file.

赞(0）回复(0）举报 2023-05-28

我来回答

SQL Server SSIS - how can I split an XML record into multiple records with different values for each column?

2条答案

相关问题

热门标签

最新问答