Parsing XML with SQL Server

Question

Parsing XML with SQL Server

SQLServerQuery

Old Hand

Points: 333
More actions
September 7, 2022 at 3:18 pm

#4084980
Thanks in advance for the assistance.
There is some data on a website that I would like to import into a SQL Server database to query:
https://www.thetakeoverpanel.org.uk/disclosure/disclosure-table
The data is also presented in an XML file:
https://www.thetakeoverpanel.org.uk/new/disclosureTable/v3/disclosuretable.xml
https://www.thetakeoverpanel.org.uk/new/disclosureTable/v3/disclosuretable.xsd
I'm having difficulties parsing the data. I think I'd like three tables variables (or temp tables) (Additions, Deletions and MainTable).
Can anyone help?
Thanks,
SQLServerQuery
- This topic was modified 2 years, 2 months ago by Steve Jones - SSC Editor. Reason: correct title
September 13, 2022 at 4:09 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

What specifically have you tried? What specific errors have you received?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 2

There are some procs for working with XML. likely to want to load the XML into a variable, then have 3 queries for your specific variables that parse out the data you want.

query() - https://docs.microsoft.com/en-us/sql/t-sql/xml/query-method-xml-data-type?view=sql-server-ver16

openxml() - https://docs.microsoft.com/en-us/sql/relational-databases/xml/openxml-sql-server?view=sql-server-ver16

It's helpful for you to pick a portion of the document and explain what results you'd like to get out of this for one variable. Showing the actual inputs and outputs is helpful here.

MarkP Old Hand Points: 305 More actions · Answer 3

I'd tackle this as you've suggested in terms of three initial tables (but would need to understand their functionality).

To parse the main table, I'd denormalise each row into staging table, then I could process the data better (handling the deletes and additions).

Steve has already mentioned the two methods for parsing the data above - use those to get your data into a table and carry out your processing from there.