Is there anyway I can get XML data into a table?

Question

Post reply

Is there anyway I can get XML data into a table?

mamidimahesh

Mr or Mrs. 500

Points: 549
More actions
April 20, 2009 at 1:27 am

#129418

Is there anyway I can get XML data into a table?
Regards,
Mahesh Mamidi

Viewing 13 posts - 1 through 12 (of 12 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

If you want to know how to store it in the db then you can use xml data type.

"Keep Trying"

mamidimahesh Mr or Mrs. 500 Points: 549 More actions · Answer 2

using XML we can store full xml in one column. But I am looking at storing XML data elements into table columns. For instance I have below xml

- (ListInfo>

- (OPT xmlns="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity">

(OpportunityId>1-PP93T8

(Created>11/21/2008 02:43:13

(Modified>11/24/2008 04:56:04

(AccountId>1-42OAQ

(CurrencyCode>EUR

(/OPT>

(/ListInfo>

I wanted to store above data into a table (not a predefined table). Is there any way?

--Mahesh

mamidimahesh Mr or Mrs. 500 Points: 549 More actions · Answer 3

Can someone help me in this? It's little urgent.....

--Mahesh

Kushal s Ten Centuries Points: 1003 More actions · Answer 4

Have you tried inserting it into char / varchar column ?. I hope you want it as text .

mamidimahesh Mr or Mrs. 500 Points: 549 More actions · Answer 5

Yes, varchar columns are fine. But each element of the xml should go into separate column. But how do I extract the XML data elements into a table?

--Mahesh

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 6

You can use value() method of XQuery to retrive individual information from xml and insert it into your destination table.

"Don't limit your challenges, challenge your limits"

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 7

I might have misunderstood what you are trying to do, but have you looked up OPENXML in Books OnLine?

This enables you to open an XML document into a table structured in the same way as your document, so you can work with it,

If what you mean is that you want to save it raw, and it's big, use a text / ntext column.

.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 8

Try this

declare @x xml

set @x='

...your XML here

'

SELECT

x.y.value('declare namespace s="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity"; s:OpportunityId[1]', 'nvarchar(10)') AS OpportunityId,

x.y.value('declare namespace s="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity"; s:Created[1]', 'nvarchar(10)') AS Created,

x.y.value('declare namespace s="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity"; s:Modified[1]', 'nvarchar(10)') AS Modified,

x.y.value('declare namespace s="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity"; s:AccountId[1]', 'nvarchar(10)') AS AccountId,

x.y.value('declare namespace s="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity"; s:CurrencyCode[1]', 'nvarchar(10)') AS CurrencyCode

INTO MyNewTable

FROM @x.nodes('declare namespace s="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity";

/ListInfo/s:OPT') AS x ( y )

____________________________________________________

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

mamidimahesh (4/20/2009)
using XML we can store full xml in one column. But I am looking at storing XML data elements into table columns. For instance I have below xml
- (ListInfo>
- (OPT xmlns="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity">
(OpportunityId>1-PP93T8
(Created>11/21/2008 02:43:13
(Modified>11/24/2008 04:56:04
(AccountId>1-42OAQ
(CurrencyCode>EUR
(/OPT>
(/ListInfo>
I wanted to store above data into a table (not a predefined table). Is there any way?
--Mahesh

If you want to store the xml elements and not the xml a columns with the appropriate data types will do. If its a int data use a int column and so on. You can use OPENXML to parse the xml and store it into the table.

What do you mean by "not a predefined table" ?

"Keep Trying"

Mauve SSChampion Points: 11316 More actions · Answer 10

Chirag (4/22/2009)
You can use OPENXML to parse the xml and store it into the table.

Don't use OPENXML! Use XQuery. See the following SQL ServerCentral article:

http://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

oedwards SSC Enthusiast Points: 111 More actions · Answer 11

There is a good article in books on line that gives examples of "Performing Bulk Load of XML Data"

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlxml49/html/3708b493-322e-4f3c-9b27-441d0c0ee346.htm

ChiragNS One Orange Chip Points: 26137 More actions · Answer 12

JohnG (4/23/2009)
Chirag (4/22/2009)
You can use OPENXML to parse the xml and store it into the table.
Don't use OPENXML! Use XQuery. See the following SQL ServerCentral article:
http://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx

thanks for the link.

"Keep Trying"