Post reply

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

  • April 20, 2009 at 1:27 am

    #129418

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

    Regards,

    Mahesh Mamidi

  • April 20, 2009 at 1:52 am

    #980541

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

    "Keep Trying"

  • April 20, 2009 at 2:29 am

    #980552

    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

  • April 20, 2009 at 4:22 am

    #980592

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

    --Mahesh

  • April 20, 2009 at 4:56 am

    #980601

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

  • April 20, 2009 at 5:02 am

    #980606

    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

  • April 20, 2009 at 6:00 am

    #980638

    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"

  • April 20, 2009 at 6:10 am

    #980644

    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.

    .

  • April 20, 2009 at 6:30 am

    #980654

    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

  • April 22, 2009 at 5:41 am

    #982085

    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"

  • April 23, 2009 at 7:44 am

    #982890

    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://qa.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]

  • April 23, 2009 at 11:05 am

    #983119

    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

  • April 24, 2009 at 12:23 am

    #983389

    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://qa.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx

    thanks for the link.

    "Keep Trying"

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

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