March 2, 2023 at 6:26 am
Hi everyone,
I have a XML file from which I am trying to import data into SQL tables.
Some nodes in that file has data of length greater than 4000 chars. My tables field are all nvarchar(max).
I am using using SSIS package's XML source to access the XML file.
Is there anyway I can divide this data in chunks and insert into multiple rows?
I receive error on the XML source before I can perform any transformation on the data.
Here is a sample data:
<Individual>
<AllNames>Organisation Name: MYANMAR ECONOMIC HOLDINGS PUBLIC COMPANY LTD</AllNames><MainInfo>a.k.a:|MEHL|Myanmar Economic Holdings LimitedAddress:189/191 Mahaban Doola Road, Corner of 50th Street, Yangon, Myanmar.</MainInfo>
<OtherInfo>Other Information: (UK Sanctions List Ref):GHR0080 Names of Director(s)/Management: Commander-in-Chief Senior General Min Aung; Deputy Commander-in-Chief U Soe Win; Lt General Hsan Oo; Lt General Mya Tun Oo; Admiral Tin Aung San; General Maung Maung Kyaw; Lt General (retired) Nyo Saw; Maj General Khin Maung Than; Maj General Moe Myint Htun; Read Admiral Moe Aung; Lt General Htun Aung; Lt General Min Naung; Lt General Aung Lin Dwe; Big General (Ret) Kyaw Htin; Major Ni Aung; Big General (Ret) Kyaw Myo Win; Maj (Retired) Ming Khine; Colonel Myint Swe; Lt General Aye Win; Ultimate beneficial owner(s): Myanmar senior military leadership, units and battalions. (UK Statement of Reasons):Myanmar Economic Holdings Limited (MEHL) is a major Myanmar conglomerate, owned by the Myanmar military and its current and former personnel. Min Aung Hlaing, Myanmar’s Commander in Chief, is the Chair of MEHL’s ‘patron group’, along with other senior ranking Tatmadaw officers. In 2017, MEHL directly contributed to a series of fundraising events, which provided financial support for the Tatmadaw personnel engaged in “clearance operations” against the Rohingya. There are reasonable grounds to suspect that part or all of these funds contributed to operations that resulted in serious human rights violations, including mass unlawful killings, torture, systematic rape and other forms of targeted sexual violence by the Tatmadaw committed in Rakhine State in 2017. In view of the circumstances including the close connections between MEHL and senior members of the Tatmadaw, there are reasonable grounds to suspect that MEHL knew or had reasonable cause to suspect that the funds would or may contribute to the serious human rights violations committed. Further or alternatively, MEHL is associated with the Commander in Chief and Deputy Commander in Chief of the Tatmadaw in view of their connections to MEHL including their positions on the patron group. (Type of entity):Military Holding Company (Subsidiaries):Adipati Agricultural Produce Trading Ltd. ASHOK (Gems and Jewellery) Co., Ltd. (alternate spelling: Thawka). Aung Thitsa Oo General Insurance Company Limited. Aung Thitsa Oo Life Insurance Company Limited. Bandoola Transportation Company Inc. Berger Paints Manufacturing Limited. Bo Aung Kyaw Terminal. Cancri (Gems and Jewellery) Co., Ltd. (alternate spelling: Phu Sha Star). Da Na Theiddi Kyal (Jewellery) Co., Ltd. (alternate spelling: Da Na Theiddihi Star and Danatheidi Star (Gems and Jewellery) Co., Ltd). Du Won Kyal (Jewellery) Co., Ltd. (alternate spelling: Du Won Star and Du Won Star (Gems & Jewellery) Co., Ltd.). Hawk Star (Gems and Jewellery) Co., Ltd. (alternate spellings: Thine Ngat Kyal (Jewellery) Co., Ltd. and Thein Nget Star). Hlaing Inland Terminal and Logistics Co., Ltd.. Inndagaw Industrial Complex; Kanpauk Oil Palm Estate and Palm Oil Mill Project (KOPP). Kayah State Mineral Production Company Ltd. Kone Yar Thi Star (alternate spelling: Aquarii (Gems & Jewellery) Co., Ltd.. Lann Pyi Marine Company Ltd; Larbathakedi Micro Finance Service Association Inc.. Lyrae (Gems and Jewellery) Co., Ltd. (alternate spelling: Saung Tar Yar Star). Mon Hsu Jewellery Co., Ltd. (alternate spelling: Mine Shu). Myanmar Imperial Jade (Gems & Jewellery) Co., Ltd.. Myanmar Land and Development Ltd. Myanmar Rubber Wood Co., Ltd.. Myanmar Ruby Enterprise (Gems & Jewellery) Co., Ltd.. Myanmar Tharkaung Finance Co., Ltd.. Myawaddy Agricultural Services Col, Ltd. Myawaddy Bank Ltd.. Myawaddy Clean Drinking Water Service. Myawaddy Trading Ltd; Myawaddy Travels and Tours Co., Ltd.. Myawady Football Club. Nawadae Hotel and Tourism Ltd.. Ngwe Pin Lei Livestock Breedings and Fisheries Co., Ltd.. Ngwe Pin Lei Premium Marine Products Co., Ltd.. Ngwe Pinlae Industrial Zone. Pone Nyet (Gems and Jewellery) Co., Ltd. (alternate spelling: Pone Nyat and One Nyat (Jewellery) Co., Ltd.). Pyinmabin Industrial Zone. Sabai (Jewellery) Co., Ltd. (alternate spellings: Sabae (Gems and Jewellery) Co., Ltd., and Jasmine). Seik Ta Ya Kyal (Jewellery) Co., Ltd. (alternate spellings: Si Tra Star, Seik Tra Star and Seiktra Star (Gems and Jewellery) Co., Ltd.). Shwe Gandamar International Trading Ltd. Shwe Innwa Gems (Business Reg No):156387282 </OtherInfo>
<ListedOn>Listed on: 25/03/2021 </ListedOn>
<UKListDate>UK Sanctions List Date Designated: 25/03/2021 </UKListDate>
<LastUpd>Last Updated: 11/11/2022 </LastUpd
><GrpID>Group ID: 14080.</GrpID>
</Individual>
Data of <OtherInfo> node is greater than 4000.
March 2, 2023 at 7:25 am
Looking at the provided data, someone had to produce XML, but missed the moint of it
<GrpID>Group ID: 14080.</GrpID>
should in fact be <GrpID>14080</GrpID> ( unless the "." has an actual significance, then it should be added too )
I'm not much of an SSIS addict, but did you try this openquery alternative ?
CREATE TABLE Individual
(
AllNames INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME not null default getdate()
)
INSERT INTO Individual(XMLData)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(BULK 'C:\SSCXMLFiles\SSC20230302.xml', SINGLE_BLOB) AS x;
SELECT *
FROM Individual
SELECT Indiv.AllNodes.query('./OtherInfo') as OtherInfo
FROM Individual
cross apply XMLData.nodes( '/Individual') AS Indiv(AllNodes);
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply