Parsing XML data into multiple Datasets

Question

Hi,
Thanks in advance for your help. I’m trying to download some data from a website and import it into a SQL Database Here is the website:
[Disclosure Table – The Takeover Panel][1]
The website posts the data in multiple formats; however I think the most useful format they provide (for this use case) is the XML version (Found below):
https://www.thetakeoverpanel.org.uk/new/disclosureTable/v3/disclosuretable.xml
The XLM file has multiple sections which I’d like to parse into multiple datasets (Table variables or Temp tables):
[additions] [deletions], [amendments] and [maintable]

DECLARE @Additions AS TABLE  
(  
[RowID] INT IDENTITY(1,1),  
[CaseID] INT,  
[Offeree_Name] NVARCHAR(MAX),  
[Offer_Period_Commenced] DATETIME,  
[Name] NVARCHAR(250),  
[ISIN] NVARCHAR(150),  
[NSI] NVARCHAR(150),  
[Offeror_Name] NVARCHAR(MAX),  
[Offeror_Rule_26_Deadline] NVARCHAR(100),  
[offeror_identified] DATETIME,  
[offeror_empty_2.10] NVARCHAR(MAX)  
)  

  
DECLARE @Deletions AS TABLE  
(  
[RowID] INT IDENTITY(1,1),  
[CaseID] INT,  
[Offeree_Name] NVARCHAR(MAX),  
[Offer_Period_Commenced] DATETIME,  
[Name] NVARCHAR(250),  
[ISIN] NVARCHAR(150),  
[NSI] NVARCHAR(150),  
[Offeror_Name] NVARCHAR(MAX),  
[Offeror_Rule_26_Deadline] NVARCHAR(100),  
[offeror_identified] DATETIME,  
[offeror_empty_2.10] NVARCHAR(MAX)  
)  

  
DECLARE @MainTable AS TABLE  
(  
[RowID] INT IDENTITY(1,1),  
[CaseID] INT,  
[Offeree_Name] NVARCHAR(MAX),  
[Offer_Period_Commenced] DATETIME,  
[Name] NVARCHAR(250),  
[ISIN] NVARCHAR(150),  
[NSI] NVARCHAR(150),  
[Offeror_Name] NVARCHAR(MAX),  
[Offeror_Rule_26_Deadline] NVARCHAR(100),  
[offeror_identified] DATETIME,  
[offeror_empty_2.10] NVARCHAR(MAX)  
)  

Each dataset has the same format.
I’ve been able to extract individual nodes (ISIN) in this example:

SELECT  
T.C.value('.', 'varchar(100)') AS [maintable_ISIN]  
FROM  
@myDoc.nodes('(/disclosure_table/maintable/case/offeree/twoten_information/twoten_line/ISIN)') as T(C)  

But I’d like to extract the whole thing directly into their relevant datasets so that they look like the attached screenshot (Example_Results.png).
Can anyone help me extract the data into separate datasets?
Thanks
[1]: https://www.thetakeoverpanel.org.uk/disclosure/disclosure-table

Answer 1

Hi @SQL ServerQuery
Try this query:

SELECT  
  ROW_NUMBER()OVER(ORDER BY Caseid.value('@caseid[1]', 'int')) AS RowID,  
  Caseid.value('@caseid[1]', 'int') AS caseid,  
  offeree.value('@name[1]', 'NVARCHAR (200)') AS Offeree_Name,  
  offeree.value('@offer_period_commenced[1]', 'VARCHAR(20)') AS Offer_Period_Commenced,  
  twoten_information.value('name[1]', 'VARCHAR(20)') AS Name,  
  twoten_information.value('ISIN[1]', 'VARCHAR(20)') AS ISIN,  
  twoten_information.value('NSI[1]', 'VARCHAR(20)') AS NSI,  
  offeror.value('@name[1]', 'NVARCHAR (200)') AS Offeror_Name,  
  offeror.value('@rule_26_deadline[1]', 'NVARCHAR (200)') AS Offeror_rule_26_deadline,  
  offeror.value('@offeror_identified[1]', 'NVARCHAR (200)') AS Offeror_identified,  
  offeror.value('empty_2.10[1]', 'NVARCHAR (200)') AS [Offeror_empty_2.10]  
FROM @myDoc.nodes('/disclosure_table/maintable/case') AS XMLtable1(Caseid)  
CROSS APPLY Caseid.nodes('offeree') XMLtable2(offeree)  
CROSS APPLY Caseid.nodes('offeror') XMLtable3(offeror)  
CROSS APPLY offeree.nodes('twoten_information/twoten_line') XMLtable4(twoten_information)  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.