May 28, 2014 at 1:26 am
I have the following XML file and want inserting into a SQL table, the problem I am having is that the XML has two row nodes.
These need to be inserted into one row, when I code the insert the xml is spread over two rows.
Could any body help.
<?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>
May 28, 2014 at 5:20 am
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
June 5, 2014 at 3:44 pm
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
June 5, 2014 at 3:55 pm
clucasi (5/28/2014)
...the problem I am having is that the XML has two row nodes.
The deal with two nodes with the same name you could refer to them by their position. In this code:
SELECTx.value('(/CommonEventData/Row[1]/CallSource)[1]','NVARCHAR(50)') AS CallSource,
x.value('(/CommonEventData/Row[1]/CallerCityStateZipCode)[1]','NVARCHAR(50)') AS CallerCityStateZipCode,
x.value('(/CommonEventData/Row[1]/CallerName)[1]','NVARCHAR(50)') AS CallerName,
x.value('(/CommonEventData/Row[1]/CallerPhone)[1]','NVARCHAR(50)') AS CallerPhone,
x.value('(/CommonEventData/Row[2]/Beat)[1]','NVARCHAR(50)') AS Beat,
x.value('(/CommonEventData/Row[2]/MultiEventId)[1]','NVARCHAR(50)') AS MultiEventId,
x.value('(/CommonEventData/Row[2]/PrimaryUnitId)[1]','NVARCHAR(50)') AS PrimaryUnitId
FROM (SELECT @TXML) x(x)
In this code, Row[1] is the First instance of the "row" node. Row[2] is the Second instance of the 'row' node.
Since the children of row are unique (e.g. callerName, CallerPhone, etc only appear once) you can refer only to row and let the XML parser sort it out like so...
SELECTx.value('(/CommonEventData/Row/CallSource)[1]','NVARCHAR(50)') AS CallSource,
x.value('(/CommonEventData/Row/CallerCityStateZipCode)[1]','NVARCHAR(50)') AS CallerCityStateZipCode,
x.value('(/CommonEventData/Row/CallerName)[1]','NVARCHAR(50)') AS CallerName,
x.value('(/CommonEventData/Row/CallerPhone)[1]','NVARCHAR(50)') AS CallerPhone,
x.value('(/CommonEventData/Row/Beat)[1]','NVARCHAR(50)') AS Beat,
x.value('(/CommonEventData/Row/MultiEventId)[1]','NVARCHAR(50)') AS MultiEventId,
x.value('(/CommonEventData/Row/PrimaryUnitId)[1]','NVARCHAR(50)') AS PrimaryUnitId
FROM (SELECT @TXML) x(x)
What I posted above is the most simplified.
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply