converting an XML file to a table

Question

converting an XML file to a table

bg1987

SSC Rookie

Points: 35
More actions
July 7, 2008 at 6:12 am

#187801

I have an XML file I want to transform into a table.
The file I have looks something like this:
(Calls_Log)
(Call)
(OperatorsName)John Doe(/OperatorsName)
(Telephone)1234567(/Telephone)
(Date)28/05/2008(/Date)
(Transcript)The client complained and complained(/Transcript)
(/Call)
.
.
.
(Calls_Log)
I want to transform this xml into a table that will treat each Call element as a row in the table, and the Child Eelements of the Call Elements as its Columns
something of this sort:
OperatorsName | Telephone | Date | Transcript
----------------------------------------------------------------------------
John Doe |1234567 |28/05/2008|The client complained and complained
edit: the brackets are instead of xml tags because the forums wont show normal tags.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Mark Beringer-730383 SSCommitted Points: 1824 More actions · Answer 1

Use the OPENXML command... Create a SP that can receive the XML as either text or xml type and then use the OPENXML command. Your code should look something like this:

DECLARE @idoc INT

declare @XmlFile xml

SET @XmlFile = '

'

EXEC sp_xml_preparedocument @idoc OUTPUT, @XmlFile, @xmlnamespace

INSERT INTO [Table]

(OperatorsName ,Telephone ,Date, Transcript)

SELECT

OperatorsName,

Telephone,

Date,

Transcript,

FROM

OPENXML(@idoc, '/Calls_Log/Call',2)

WITH(

OperatorsName VARCHAR(50),

Telephone VARCHAR(50),

Date VARCHAR(50),

Transcript VARCHAR(50)

)

-- Release resources used by XML document

EXEC sp_xml_removedocument @idoc

Check OPENXML on BOL for more examples.

hr_sn Ten Centuries Points: 1122 More actions · Answer 2

Assuming you are using SQL2K5 and you got this xml in variable called @xml of type xml, try this

SELECTcalls.cLog.value('OperatorsName[1]', 'varchar(512)') as OperatorsName

,calls.cLog.value('Date[1]', 'varchar(512)') as Date

,calls.cLog.value('Transcript[1]', 'varchar(512)') as Transcript

FROM@xml.nodes('//Call') as calls(cLog)

You can modify this to any extent, do some research on xquery in BOL

bg1987 SSC Rookie Points: 35 More actions · Answer 3

hr_sn (7/7/2008)
Assuming you are using SQL2K5 and you got this xml in variable called @xml of type xml, try this
SELECTcalls.cLog.value('OperatorsName[1]', 'varchar(512)') as OperatorsName
,calls.cLog.value('Date[1]', 'varchar(512)') as Date
,calls.cLog.value('Transcript[1]', 'varchar(512)') as Transcript
FROM@xml.nodes('//Call') as calls(cLog)
You can modify this to any extent, do some research on xquery in BOL

I used this method but now i have another question, say the Calls_Log element has a LogID attribute, and I want my query to return that info as well as the other elements in the xml, how would I do that?

Mark Cowne One Orange Chip Points: 26982 More actions · Answer 4

SELECT calls.cLog.value('OperatorsName[1]', 'varchar(512)') as OperatorsName

, calls.cLog.value('Date[1]', 'varchar(512)') as Date

, calls.cLog.value('Transcript[1]', 'varchar(512)') as Transcript

, calls.cLog.value('../@LogID', 'varchar(512)') as LogID

FROM @xml.nodes('//Call') as calls(cLog)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

bg1987 SSC Rookie Points: 35 More actions · Answer 5

bg1987

SSC Rookie

Points: 35

July 9, 2008 at 5:20 am

#839987

Works perfectly! Thanks a lot!

VIPS SSC Enthusiast Points: 104 More actions · Answer 6

is there a way that I can covert multi table data into single table with one xml column by just providing the table name?

like table1 (col1 int, col2 date);

table2(col3 varchar(10), col4 numeric)

put all the rows of these 2 tables in another table

table_xml(table_name varchar(200), data_row xml)

hr_sn Ten Centuries Points: 1122 More actions · Answer 7

VIPS (7/11/2008)
is there a way that I can covert multi table data into single table with one xml column by just providing the table name?

Yes, this is absolutely possible but you need to provide some more info on what you are trying to achieve.

VIPS SSC Enthusiast Points: 104 More actions · Answer 8

I am trying to get some dynamic error processing... where if error occurs on any procedure writing to any table then the error writes the whole table row data as XML in one place (alongwith other process related info). That one place conceivable is a table with one of the columns as XML datatype.

is there a way that I can covert multi table data into single table with one xml column by just providing the table name?
like table1 (col1 int, col2 date);
table2(col3 varchar(10), col4 numeric)
put all the rows of these 2 tables in another table
table_xml(table_name varchar(200), data_row xml)

MuditGupta SSCrazy Points: 2596 More actions · Answer 9

have u tried creating ssis package for same?

I think you achieve your desired output.

VIPS SSC Enthusiast Points: 104 More actions · Answer 10

I want to write a procedure to pass on the table name and the PK of that table to do that. if possible.