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