Importing XML data to SQL tables

Question

Importing XML data to SQL tables

b_boboci

Valued Member

Points: 67
More actions
April 10, 2009 at 2:14 am

#130003

Any idea how to import this data with a SQL Query.
I am finding some solutions, but nothing for this case.
thanks.
Example is in Attachment(Example1.txt)

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

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

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 1

Please give the structure of the table into which you wish to import this XML data, preferably in the form of a CREATE TABLE statement.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Pleas give us more details. Do you want to import it as an XML column or do you want each value to go into a column? If each tag has to go into a different column – Are all the columns in the same table or are they in different tables? What did you try to do and what errors or difficulties did you encounter?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

b_boboci Valued Member Points: 67 More actions · Answer 3

declare @tabela1 table (

ChargedUserID int,

ChargedUserType varchar(100),

SubscriberName varchar(100),

TrunkID int,

Date datetime,

Time datetime,

CallDuration datetime,

DialledNumber varchar(100),

DiallingMode varchar(100)

)

this is my table.

i want to insert those data to each column,

this xml file is to small, just to demonstrate.

thanks

b_boboci Valued Member Points: 67 More actions · Answer 4

i want to insert into one table.

SELECT c.value(N'(Accounting/ChargedUserID)[1]', N'nvarchar(100)') AS ChargedUserID,

c.value(N'(Accounting/ChargedUserType)[1]', N'nvarchar(100)') AS ChargedUserType,

c.value(N'(Accounting/SubscriberName)[1]', N'nvarchar(100)') AS SubscriberName,

c.value(N'(Accounting/TrunkID)[1]', N'int') AS TrunkID,

c.value(N'(Accounting/Date)[1]', N'datetime') AS Date,

c.value(N'(Accounting/Time)[1]', N'datetime') AS Time,

c.value(N'(Accounting/CallDuration)[1]', N'datetime') AS CallDuration,

c.value(N'(Accounting/DialledNumber)[1]', N'nvarchar(100)') AS DialledNumber,

c.value(N'(Accounting/DiallingMode)[1]', N'nvarchar(100)') AS DiallingMode

FROM @xmlja.nodes(N'//AccountingList') t(c);

I tryed this, but this code return only first row.

My xml file is very big. so it has a lot of data...

I think the problem is [1], this must return first row.

b_boboci Valued Member Points: 67 More actions · Answer 5

b_boboci

Valued Member

Points: 67

April 10, 2009 at 2:52 am

#975610

Here is original copy of xml file.

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 6

I think this will work.

SELECT

c.value(N'ChargedUserID[1]', N'nvarchar(100)') AS ChargedUserID,

c.value(N'ChargedUserType[1]', N'nvarchar(100)') AS ChargedUserType,

c.value(N'SubscriberName[1]', N'nvarchar(100)') AS SubscriberName,

c.value(N'TrunkID[1]', N'int') AS TrunkID,

c.value(N'Date[1]', N'datetime') AS Date,

c.value(N'Time[1]', N'datetime') AS Time,

c.value(N'CallDuration[1]', N'datetime') AS CallDuration,

c.value(N'DialledNumber[1]', N'nvarchar(100)') AS DialledNumber,

c.value(N'DiallingMode[1]', N'nvarchar(100)') AS DiallingMode

FROM @xmlja.nodes(N'/CallAccountingList/CallAccounting') t(c);

EDIT: Changed @xmlja.nodes(N'/AccountingList/Accounting') to @xmlja.nodes(N'/CallAccountingList/CallAccounting') to match supplied XML example.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 7

Your query doesn’t match the XML structure that you showed us. My answer is based on the XML that you attached and not on your query. If the hierarchy in the real XML is the same as in the one that you attched, you have a mistake in the XQuery expression that you specified in the node operator. Try to modify the XQuery expression so it will show you the correct level in the hierarchy that you want to look at (N'/AccountingList/Accounting' instead of N'//AccountingList'). After that you’ll need to modify the XQuery expression in the value method. Since the product of node operator is a table with XML that includes only the part begins and ends with Accounting tag, you need to drop part that is above the accounting in the hirarchey in the source XML (AccountingList). In short try to run this query:

SELECT c.value(N'(/ChargedUserID)[1]', N'nvarchar(100)') AS ChargedUserID,

c.value(N'(ChargedUserType)[1]', N'nvarchar(100)') AS ChargedUserType,

c.value(N'(SubscriberName)[1]', N'nvarchar(100)') AS SubscriberName,

c.value(N'(TrunkID)[1]', N'int') AS TrunkID,

c.value(N'(Date)[1]', N'datetime') AS Date,

c.value(N'(Time)[1]', N'datetime') AS Time,

c.value(N'(CallDuration)[1]', N'datetime') AS CallDuration,

c.value(N'(DialledNumber)[1]', N'nvarchar(100)') AS DialledNumber,

c.value(N'(DiallingMode)[1]', N'nvarchar(100)') AS DiallingMode

FROM @xml.nodes(N'/AccountingList/Accounting') t(c);

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

b_boboci Valued Member Points: 67 More actions · Answer 8

Yes, u are great guys.

The problem was in @xmlja.nodes(N'/CallAccountingList/CallAccounting') t(c);

Thanks a lot 🙂