Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 20, 2016 3:45 AM
Points: 100, Visits: 465
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
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 5,557, Visits: 14,752
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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:48 PM
Points: 1,825, Visits: 6,136
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove of replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Post #1578099
Posted Thursday, June 5, 2014 3:55 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:48 PM
Points: 1,825, Visits: 6,136
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove of replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Post #1578103
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse