Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML To SQL conversion Expand / Collapse
Author
Message
Posted Wednesday, May 28, 2014 1:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:24 AM
Points: 94, Visits: 439
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>
Post #1575063
Posted Wednesday, May 28, 2014 5:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 1,904, Visits: 5,238
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
Post #1575130
Posted Thursday, June 5, 2014 3:44 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:06 PM
Points: 581, Visits: 2,711
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:

SELECT	x.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)


SELECT	x.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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1578099
Posted Thursday, June 5, 2014 3:55 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:06 PM
Points: 581, Visits: 2,711
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:

SELECT	x.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...

SELECT	x.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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1578103
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse