Eirikur Eiriksson (5/28/2014)
Here is a very simple approach😎
USE tempdb;GO
DECLARE @TXML XML = N'<?xml version="1.0"?>
<CommonEventData>
<Row>
<CallSource>999</CallSource>
<CallerCityStateZipCode>
</CallerCityStateZipCode>
<CallerName>TEST</CallerName>
<CallerPhone>TEST</CallerPhone>
</Row>
<Row>
<Beat>E27</Beat>
<MultiEventId>0</MultiEventId>
<PrimaryUnitId>E09A1</PrimaryUnitId>
</Row>
</CommonEventData>'
SELECT
MAX(RO.W.value('CallSource[1]','NVARCHAR(50)') ) AS CallSource
,MAX(RO.W.value('CallerCityStateZipCode[1]','NVARCHAR(50)') ) AS CallerCityStateZipCode
,MAX(RO.W.value('CallerName[1]','NVARCHAR(50)') ) AS CallerName
,MAX(RO.W.value('CallerPhone[1]','NVARCHAR(50)') ) AS CallerPhone
,MAX(RO.W.value('Beat[1]','NVARCHAR(50)') ) AS Beat
,MAX(RO.W.value('MultiEventId[1]','NVARCHAR(50)') ) AS MultiEventId
,MAX(RO.W.value('PrimaryUnitId[1]','NVARCHAR(50)') ) AS PrimaryUnitId
FROM @TXML.nodes('CommonEventData') AS CE(DT)
OUTER APPLY CE.DT.nodes('Row') AS RO(W)
Results
CallSource CallerCityStateZipCode CallerName CallerPhone Beat MultiEventId PrimaryUnitId----------- ----------------------- ----------- ------------ ----- ------------- --------------
999 TEST TEST E27 0 E09A1
Can be even further simplified like so:
SELECTx.value('(//CallSource)[1]','NVARCHAR(50)') AS CallSource,
x.value('(//CallerCityStateZipCode)[1]','NVARCHAR(50)') AS CallerCityStateZipCode,
x.value('(//CallerName)[1]','NVARCHAR(50)') AS CallerName,
x.value('(//CallerPhone)[1]','NVARCHAR(50)') AS CallerPhone,
x.value('(//Beat)[1]','NVARCHAR(50)') AS Beat,
x.value('(//MultiEventId)[1]','NVARCHAR(50)') AS MultiEventId,
x.value('(//PrimaryUnitId)[1]','NVARCHAR(50)') AS PrimaryUnitId
FROM (SELECT @TXML) x(x)
SELECTx.value('(//CallSource/text())[1]','NVARCHAR(50)') AS CallSource,
x.value('(//CallerCityStateZipCode/text())[1]','NVARCHAR(50)') AS CallerCityStateZipCode,
x.value('(//CallerName/text())[1]','NVARCHAR(50)') AS CallerName,
x.value('(//CallerPhone/text())[1]','NVARCHAR(50)') AS CallerPhone,
x.value('(//Beat/text())[1]','NVARCHAR(50)') AS Beat,
x.value('(//MultiEventId/text())[1]','NVARCHAR(50)') AS MultiEventId,
x.value('(//PrimaryUnitId/text())[1]','NVARCHAR(50)') AS PrimaryUnitId
FROM (SELECT @TXML) x(x)
EDIT: Changed my code to include the text() node. This is vital for performance.
-- Itzik Ben-Gan 2001