quote:

---

Originally posted by jackv

If you'd like to shred xml with SQL Server - follow these instructions\example - http://www.sqlserver-dba.com/2013/05/shredding-xml-with-powershell-and-sql-server.html
It's a common problem to extract xml and place into EAV tables

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

---

declare @xml xml = 
'<MachineRepostitoryDS xmlns="http://Testing.com/Deployment/MachineRepostitoryDS.xsd">
<Components>
<Environment>QA400</Environment>
<HostName>HostTesting</HostName>
<Component>EventProcessorService</Component>
<CompStatus>OK. 17 Testing assemblies found</CompStatus>
<Version>4.0.24.0</Version>
<Path>D:\Testing\EventProcessor.WinService</Path>
</Components>
</MachineRepostitoryDS>'
;
with XMLNAMESPACES  (default  'http://Testing.com/Deployment/MachineRepostitoryDS.xsd')
select n.value('Version[1]', 'nvarchar(20)')
from @xml.nodes('//Components') x(n)