-- variables DECLARE @xml XML = ' <dinosaurs> <d> <name>Aachenosaurus</name> <url>http://en.wikipedia.org/wiki/Aachenosaurus</url> </d> <d> <name>Aardonyx</name> <url>http://en.wikipedia.org/wiki/Aardonyx</url> </d> <d> <name>Abdallahsaurus</name> <url>http://en.wikipedia.org/wiki/Abdallahsaurus</url> </d> <d> <name>Abelisaurus</name> <url>http://en.wikipedia.org/wiki/Abelisaurus</url> </d> <d> <name>Abrictosaurus</name> <url>http://en.wikipedia.org/wiki/Abrictosaurus</url> </d> <d> <name>Abrosaurus</name> <url>http://en.wikipedia.org/wiki/Abrosaurus</url> </d> <d> <name>Abydosaurus</name> <url>http://en.wikipedia.org/wiki/Abydosaurus</url> </d> <d> <name>Acanthopholis</name> <url>http://en.wikipedia.org/wiki/Acanthopholis</url> </d> <d> <name>Achelousaurus</name> <url>http://en.wikipedia.org/wiki/Achelousaurus</url> </d> <d> <name>Achillesaurus</name> <url>http://en.wikipedia.org/wiki/Achillesaurus</url> </d> </dinosaurs>' -- use common table expression to shred data ;WITH ShreddedData (name, url) AS ( SELECT x.node.value('name[1]', 'VARCHAR(255)'), x.node.value('url[1]', 'VARCHAR(255)') FROM @xml.nodes('dinosaurs/d') x(node) ) SELECT * FROM ShreddedData;
Shredding XML data (elements) using SQL Server
Here's a simple example of shredding XML data (elements) in SQL Server.
0 comments: