Xml Shredder

SELECT
[Parent] = T.C.value('local-name(../../..)', 'varchar(50)')
, [ParentID] = T.C.value('../../../@ID', 'uniqueidentifier')
, [Node] = T.C.value('local-name(..)', 'varchar(50)')
, [ID] = T.C.value('../@ID', 'uniqueidentifier')
, [ChildNode] = T.C.value('local-name(.)', 'VARCHAR(50)')
, [Value] = T.C.value('.', 'nvarchar(max)')
FROM @xml.nodes('//Transfer/*/*/*') T(C);

Terremotos Iquique

Terremotos Iquique durante Marzo y Abril

 

http://earthquake.usgs.gov/earthquakes/map/#%7B%22feed%22%3A%2230day_m25%22%2C%22search%22%3Anull%2C%22sort%22%3A%22newest%22%2C%22basemap%22%3A%22terrain%22%2C%22autoUpdate%22%3Atrue%2C%22restrictListToMap%22%3Atrue%2C%22timeZone%22%3A%22local%22%2C%22mapposition%22%3A%5B%5B-21.081937360616074%2C-72.16094970703125%5D%2C%5B-18.88030044453507%2C-68.64532470703125%5D%5D%2C%22overlays%22%3A%7B%22plates%22%3Atrue%7D%2C%22viewModes%22%3A%7B%22map%22%3Atrue%2C%22list%22%3Atrue%2C%22settings%22%3Atrue%2C%22help%22%3Afalse%7D%7D

The FOR XML clause is not allowed in a INSERT statement

DECLARE @sql nvarchar(max);
DECLARE @TRANSFER_TYPE_CD nvarchar(50) = 'SF1151';
DECLARE @Transfers XML, @results XML;

DECLARE @tmpTable TABLE (data XML);
DECLARE @nodes TABLE (match XML);

INSERT @tmpTable SELECT ('');
SELECT * FROM @tmpTable;

-- wrap select with select()
select @sql = N'
DECLARE @TRANSFER_TYPE_CD nvarchar(50)=''' + @TRANSFER_TYPE_CD + ''';
SELECT(SELECT
[TRANSFER_XML].query(''//Transfer[@Type=sql:variable("@TRANSFER_TYPE_CD")]'')
FROM [dbo].[IMART_TRANSFER]
FOR XML PATH(''''))';

INSERT into @nodes exec(@sql);
SELECT * from @nodes;

SELECT @results = (SELECT * from @nodes);

UPDATE @tmpTable
SET data.modify('insert sql:variable("@results") into (/root/Transfers)[1]');
SELECT * FROM @tmpTable;