Extract/Normalise XML data

Question

Extract/Normalise XML data

MacDaddy

SSC Eights!

Points: 993
More actions
November 20, 2018 at 3:40 am

#376442

I have a table with an XML field I need to extract and normalise, maybe dumping the data into a new table in the process.
I need to keep the primary key, which is just an integer, and extract the following XML into however many rows are needed from the following, as a sample:
<Notes xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Note DateCreated="2017-04-20T09:24:54.4122417+01:00" CreatedBy="DOMAIN\UserName1" StatusChange="Disable">note text goes in here</Note>
<Note DateCreated="2014-05-01T12:17:18.1825285+01:00" CreatedBy="Domain\UserName2" StatusChange="None">second note text goes in here</Note>
</Notes>
So, if this XML ^ existed for a PK of 123456 I'd ideally want an output like this:
The number of XML rows is variable for each Id and for some Ids there might not be any at all.
Is this possible to extract and normalise? If so, how?
Thanks in advance
Keep the rubber side down and the shiny side up.

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

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

When posting, you should really show your attempts too; it really helps us understand how far you've got and may mean that we don't have to do all the work for you as you may well nearly almost be there.

Anyway, for the data supplied:

USE Sandbox;
GO
CREATE TABLE dbo.XmlTable (ID int,XMLData xml);
INSERT INTO dbo.XmlTable (ID,XMLData)
VALUES(123456,
'<Notes xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <Note DateCreated="2017-04-20T09:24:54.4122417+01:00" CreatedBy="DOMAIN\UserName1" StatusChange="Disable">note text goes in here</Note>
 <Note DateCreated="2014-05-01T12:17:18.1825285+01:00" CreatedBy="Domain\UserName2" StatusChange="None">second note text goes in here</Note>
</Notes>');
GO
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema' AS xsd, 'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
--The above isn't actually needed, but force of habit
SELECT XT.ID,
   XD.N.value('@DateCreated','datetimeoffset(7)') AS DateCreated,
   XD.N.value('@CreatedBy','sysname') AS CreatedBy,
   XD.N.value('@StatusChange','varchar(10)') AS StatusChange,
   XD.N.value('(./text())[1]','varchar(100)') AS Notes
FROM dbo.XmlTable XT
  CROSS APPLY XT.XMLData.nodes('/Notes/Note') XD(N);
GO
DROP TABLE dbo.XmlTable;

It's important you understand what this does, so if you don't understand, please do ask or look up the syntax.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

MacDaddy SSC Eights! Points: 993 More actions · Answer 2

Thom A - Tuesday, November 20, 2018 5:31 AM
When posting, you should really show your attempts too; it really helps us understand how far you've got and may mean that we don't have to do all the work for you as you may well nearly almost be there.
Anyway, for the data supplied:
USE Sandbox;
GO
CREATE TABLE dbo.XmlTable (ID int,XMLData xml);
INSERT INTO dbo.XmlTable (ID,XMLData)
VALUES(123456,
'<Notes xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Note DateCreated="2017-04-20T09:24:54.4122417+01:00" CreatedBy="DOMAIN\UserName1" StatusChange="Disable">note text goes in here</Note>
<Note DateCreated="2014-05-01T12:17:18.1825285+01:00" CreatedBy="Domain\UserName2" StatusChange="None">second note text goes in here</Note>
</Notes>');
GO
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema' AS xsd, 'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
--The above isn't actually needed, but force of habit
SELECT XT.ID,
   XD.N.value('@DateCreated','datetimeoffset(7)') AS DateCreated,
   XD.N.value('@CreatedBy','sysname') AS CreatedBy,
   XD.N.value('@StatusChange','varchar(10)') AS StatusChange,
   XD.N.value('(./text())[1]','varchar(100)') AS Notes
FROM dbo.XmlTable XT
CROSS APPLY XT.XMLData.nodes('/Notes/Note') XD(N);
GO
DROP TABLE dbo.XmlTable;
It's important you understand what this does, so if you don't understand, please do ask or look up the syntax.

Thom,
First of all, massive thanks for the code above - that's totally nailed it!
I didn't post my own efforts as I was struggling to get very far at all - I hate XML with a passion - I had just about managed to extract the note text into a continuous string (not separate rows) from each node along with the ID but was getting nowhere with extracting anything else from the XML, or putting it into a normalised format. Google had lots of resource but nothing helpful that I could understand for this purpose. However, I will bear your advice in mind when posting in the future, however embarrassing it might be.
In all honestly I don't fully understand what's happening in the code but I can see the gist of it. With a bit of practise I could probably re-apply it.

Thank you so much, you have been extremely helpful.

Chris

Keep the rubber side down and the shiny side up.

Thom A SSC Guru Points: 99133 More actions · Answer 3

MacDaddy - Tuesday, November 20, 2018 6:00 AM
Thom,
First of all, massive thanks for the code above - that's totally nailed it!
I didn't post my own efforts as I was struggling to get very far at all - I hate XML with a passion - I had just about managed to extract the note text into a continuous string (not separate rows) from each node along with the ID but was getting nowhere with extracting anything else from the XML, or putting it into a normalised format. Google had lots of resource but nothing helpful that I could understand for this purpose. However, I will bear your advice in mind when posting in the future, however embarrassing it might be.
In all honestly I don't fully understand what's happening in the code but I can see the gist of it. With a bit of practise I could probably re-apply it.
Thank you so much, you have been extremely helpful.
Chris

There's not need to be embarrassed when showing your attempts. Showing your attempts means that we can see you have tried, and sometimes those attempts can be very close to the goal (however, as it stands the results are very wrong).

if there isn't a particular part you don't understand though, please do ask. It's up to you to support the above code, not me; so you need to be able to amend it and explain to others how it works who might need to be responsible for it in the future.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk