This looks like relatively simple XML, you should just use XQuery to parse it
😎Something like this:
SELECT
REPORT.DATA.query('.')
FROM @TXML.nodes('Reports/Report') AS REPORT(DATA)
Thanks, Let me dig further from this solution.
You'll find several examples on this forum, feel free to ping back if you have any problems.
😎
Thanks for your kind help, I'm looking for getting Insolvency(Data under <a:Insolvency>) data from above script but i can not.
Here is a quick example on how to extract the insolvency data
😎
SELECT
REPORT.DATA.value('@IDNumber','VARCHAR(50)') AS IDNumber
,INSOLVENCY.DATA.value('(*:AccordingToLaw/text())[1]' ,'VARCHAR(50)') AS AccordingToLaw
,INSOLVENCY.DATA.value('(*:CreditorTermDays/text())[1]' ,'VARCHAR(50)') AS CreditorTermDays
,INSOLVENCY.DATA.value('(*:CreditorTermMonths/text())[1]' ,'VARCHAR(50)') AS CreditorTermMonths
,INSOLVENCY.DATA.value('(*:CreditorTermWeeks/text())[1]' ,'VARCHAR(50)') AS CreditorTermWeeks
,INSOLVENCY.DATA.value('(*:ProcessCharacter/text())[1]' ,'VARCHAR(50)') AS ProcessCharacter
,INSOLVENCY.DATA.value('(*:ProcessEndDate/text())[1]' ,'VARCHAR(50)') AS ProcessEndDate
,INSOLVENCY.DATA.value('(*:ProcessStartDate/text())[1]' ,'VARCHAR(50)') AS ProcessStartDate
,INSOLVENCY.DATA.value('(*:StateOfInsolvencySolution/text())[1]' ,'VARCHAR(50)') AS StateOfInsolvencySolution
,INSOLVENCY.DATA.value('(*:TypeOfProcess/text())[1]' ,'VARCHAR(50)') AS TypeOfProcess
,INSOLVENCY.DATA.query('.') AS INSOLVENCY_XML_DATA
FROM @TXML.nodes('Reports/Report') AS REPORT(DATA)
OUTER APPLY REPORT.DATA.nodes('*:CustomReport/*:Insolvencies/*:InsolvencyList/*:Insolvency') AS INSOLVENCY(DATA)
Many thanks sir..!! It worked like a charm..!!
Still one advice needed. If i have to store this is sql database, would that be advisable to store in a singlen table or i should create multiple tables.
And is it possible to get each <Report> output in one row(Or multiple rows)?