Parsing XML data to table
-
I'd like to take this XML sample
<Root>
<M-Introduction to school>
<m1>
<item Id="01" Name="Intro">
</m1>
<m2>
<item Id="02" Name="Basic training">
</m2>
</M-Introduction to school>
</Root>and convert it to the table below:
Can you please help? Thank you so much.
-
I'm not sure how much control you have over the xml, I reformatted it a bit and here is how I would do it:
declare @x xml = '<Root>
<M-Introductiontoschool>
<item XML_TagName="M-Introduction to school" subtag="m1" Id="01" Name="Intro" />
<item XML_TagName="M-Introduction to school" subtag="m2" Id="02" Name="Basic training" />
</M-Introductiontoschool>
</Root>'
SELECT
m.o.value('@XML_TagName', 'varchar(50)') as XML_TagName,
m.o.value('@subtag', 'varchar(10)') as subtag ,
m.o.value('@Id', 'int') as id ,
m.o.value('@Name', 'varchar(50)') as [Name]
FROM @x.nodes('Root/M-Introductiontoschool/item') as m(o) -
Thank you so much for your help. But I don't have control over the data and can't edit it and there are a lot of them to go through. It came from a system that I need to transfer data over to another system. I need to have a path to each id number. I just came up with a new sample. Can you try again?
Ultimately, I'd like to create three fields in table below.
DECLARE @idoc INT, @doc VARCHAR(1000), @docHandle int ;
SET @doc ='
<ROOT>
<M1-Introduction-to-school>
<c1>
<item Id="8329afwq" ChapterName="Intro"></item>
</c1>
<c2>
<item Id="0972kwnt" ChapterName="Basic training"></item>
</c2>
</M1-Introduction-to-school>
<M2-Running>
<c3>
<item Id="7391hwqj" ChapterName="Jogging"></item>
</c3>
<c4>
<item Id="7123jnwe" ChapterName="HIIT"></item>
</c4>
</M2-Running>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/M1-Introduction-to-school/c1/item',1)
WITH (Id VARCHAR(10),
ChapterName VARCHAR(20));
SELECT *
FROM OPENXML (@idoc, '/ROOT/M1-Introduction-to-school/c2/item',1)
WITH (Id VARCHAR(10),
ChapterName VARCHAR(20));
SELECT *
FROM OPENXML (@idoc, '/ROOT/M2-Running/c3/item',1)
WITH (Id VARCHAR(10),
ChapterName VARCHAR(20));
SELECT *
FROM OPENXML (@idoc, '/ROOT/M2-Running/c4/item',1)
WITH (Id VARCHAR(10),
ChapterName VARCHAR(20));
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc
SELECT distinct localname
FROM OPENXML(@docHandle, '/')
WHERE nodetype = 1 and localname <> 'ROOT'
ORDER BY localname; -
Here is what I would do. There may be a different way--without having to "hardcode" every path--but I don't know of one.
DECLARE @doc XML
SET @doc ='
<ROOT>
<M1-Introduction-to-school>
<c1>
<item Id="8329afwq" ChapterName="Intro"></item>
</c1>
<c2>
<item Id="0972kwnt" ChapterName="Basic training"></item>
</c2>
</M1-Introduction-to-school>
<M2-Running>
<c3>
<item Id="7391hwqj" ChapterName="Jogging"></item>
</c3>
<c4>
<item Id="7123jnwe" ChapterName="HIIT"></item>
</c4>
</M2-Running>
</ROOT>';
SELECT C1.x.value('@Id', 'varchar(20)') as Id
,C1.x.value('@ChapterName', 'varchar(20)') as ChapterName
,'M1-Introduction-to-school\c1\item' as [Path]
FROM @doc.nodes('ROOT/M1-Introduction-to-school/c1/item') as C1(x)
UNION
SELECT C2.x.value('@Id', 'varchar(20)') as Id
,C2.x.value('@ChapterName', 'varchar(20)') as ChapterName
,'M1-Introduction-to-school\c2\item' as [Path]
FROM @doc.nodes('ROOT/M1-Introduction-to-school/c2/item') as C2(x)
UNION
SELECT C3.x.value('@Id', 'varchar(20)') as Id
,C3.x.value('@ChapterName', 'varchar(20)') as ChapterName
,'M2-Running\c3\item' as [Path]
FROM @doc.nodes('ROOT/M2-Running/c3/item') as C3(x)
UNION
SELECT C4.x.value('@Id', 'varchar(20)') as Id
,C4.x.value('@ChapterName', 'varchar(20)') as ChapterName
,'M2-Running\c4\item' as [Path]
FROM @doc.nodes('ROOT/M2-Running/c4/item') as C4(x) -
Another way
SELECT C.x.value('@Id', 'varchar(20)') as Id
,C.x.value('@ChapterName', 'varchar(20)') as ChapterName
,C.x.value('concat(local-name(parent::*/parent::*),"\",local-name(parent::*),"\",local-name(.)) ', 'VARCHAR(1000)') AS [Path]
FROM @doc.nodes('ROOT/*/*/item') as C(x)____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you so much Mark Cowne. This is perfect. Much appreciated!
-
Thank you so much Jmetape. Much appreciated!
-
You are welcome. I learned something, too, from Mark C’s solution
- Mark Cowne wrote:
Another way
SELECT C.x.value('@Id', 'varchar(20)') as Id
,C.x.value('@ChapterName', 'varchar(20)') as ChapterName
,C.x.value('concat(local-name(parent::*/parent::*),"\",local-name(parent::*),"\",local-name(.)) ', 'VARCHAR(1000)') AS [Path]
FROM @doc.nodes('ROOT/*/*/item') as C(x)You've helped a great number of people with sometimes some incredible solutions (especially with me in the past). You should give a presentation on the subject of how to make and break XML in SQL Server. There's a call for speakers at https://www.groupby.org/ . I, for one, would vote for it and I'm pretty sure that it would be very well attended. Seriously... consider it. If you need some help, PM me. I'd be happy to help someone I respect with a subject that's so important. You might also want to consider publishing a"Stairway" here on SQL Server central.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Mark,
It looks like I have another challenge. There is another node inside Item. Can you help me to get the title in the Item node? I've updated the sample code to include Title.
Thank you.
DECLARE @doc XML
SET @doc ='
<ROOT>
<M1-Introduction-to-school>
<c1>
<item Id="8329afwq" ChapterName="Intro"></item>
</c1>
<c2>
<item Id="0972kwnt" ChapterName="Basic training">
<title>Administration</title>
</item>
</c2>
</M1-Introduction-to-school>
<M2-Running>
<c3>
<item Id="7391hwqj" ChapterName="Jogging"></item>
</c3>
<c4>
<item Id="7123jnwe" ChapterName="HIIT">
<title>High Intensity Interval training</title>
</item>
</c4>
</M2-Running>
</ROOT>';
SELECT C.x.value('@Id', 'varchar(20)') as Id
,C.x.value('@ChapterName', 'varchar(20)') as ChapterName
,C.x.value('concat(local-name(parent::*/parent::*),"\",local-name(parent::*),"\",local-name(.)) ', 'VARCHAR(1000)') AS [Path]
FROM @doc.nodes('ROOT/*/*/item') as C(x) -
@shogunSQL, this should work for you.
SELECT C.x.value('@Id', 'varchar(20)') as Id
,C.x.value('@ChapterName', 'varchar(20)') as ChapterName
,C.x.value('title[1]', 'varchar(100)') as Title
,C.x.value('concat(local-name(parent::*/parent::*),"\",local-name(parent::*),"\",local-name(.)) ', 'VARCHAR(1000)') AS [Path]
FROM @doc.nodes('ROOT/*/*/item') as C(x)@jeff - interesting idea, I'll give it some thought.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
It's amazing. Thank you so much.
Now I know why I couldn't get the Title node, I missed [1] , which it pulls from another node level based on @doc.nodes('ROOT/*/*/item') level.
-
As a perf update, you can use the following code for extracting Title.
C.x.value( '(title/text())[1]', 'varchar(100)' )
I am not an XML expert, but this was a tip that I learned from this site a while ago.
-
Thank you DesNorton. That works as well.
-
I've found it to be much more fun and useful to pork chop anyone that sends me data in XML format. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply