Inserting XML

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715375

    Comments posted to this topic are about the item Inserting XML

  • Alan Burstein

    SSC Guru

    Points: 61032

    I don't know why you get an error. This code

    CREATE TABLE Mytest (id INT, x XML)

    INSERT mytest

    VALUES

    (1, CAST('1200' AS XML))

    , (2, CAST( '1201' AS XML))

    ... works just fine on my system.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant 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.  -- Itzik Ben-Gan 2001

  • Jeff Moden

    SSC Guru

    Points: 994537

    Alan.B (5/31/2016)


    I don't know why you get an error. This code

    CREATE TABLE Mytest (id INT, x XML)

    INSERT mytest

    VALUES

    (1, CAST('1200' AS XML))

    , (2, CAST( '1201' AS XML))

    ... works just fine on my system.

    Agreed. No error in that code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • darkunic

    SSCommitted

    Points: 1888

    There is no error in this code

  • HappyGeek

    SSCoach

    Points: 18659

    darkunic (5/31/2016)


    There is no error in this code

    Agreed and that is why I read the question again. The question seemed more about 'how do I use this data to create an XML document?' rather than the code itself, so was there something else missing?

    Luckily got it right after reading the Stairway to XML articles.

    ...

  • chprasankumar

    SSC Veteran

    Points: 236

    me too found no error with this code.

  • morlindk

    SSCrazy

    Points: 2294

    Works fine for me too!

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Alan.B (5/31/2016)


    I don't know why you get an error. This code

    CREATE TABLE Mytest (id INT, x XML)

    INSERT mytest

    VALUES

    (1, CAST('1200' AS XML))

    , (2, CAST( '1201' AS XML))

    ... works just fine on my system.

    works fine on at least the last three versions of sql server.

    Igor Micev,
    My blog: www.igormicev.com

  • Toreador

    SSChampion

    Points: 11227

    I guessed the first option (about the = sign) as there was obviously something wrong with the question (the SQL will run fine) so I guessed that the typo was the omission of an '=' somewhere.

    Close, but no cigar 😉

  • RossOverThere

    SSC Veteran

    Points: 290

    Don't even know why I bothered answering this one - it was a pure guess, given that I didn't even understand half of the options, and the code doesn't cause an error!

    The explanation "The XML document needs to attribute for ID to be quoted" also doesn't make much sense, nor does it seem to relate to the question.

    Try again!

  • edwardwill

    SSCarpal Tunnel

    Points: 4779

    Another borked QotD.

  • Mauricio_

    SSCrazy

    Points: 2813

    Same here, no errors in the code.

  • J Livingston SQL

    SSC Guru

    Points: 51272

    should the code have been

    CREATE TABLE Mytest (id XML, x XML)

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Mark Fitzgerald-331224

    SSCrazy Eights

    Points: 8489

    Don't know what the question should have been for that set of answers. Certainly not an error. +1

  • Mighty

    SSCrazy Eights

    Points: 8435

    Choose the "correct" answer by eliminating the ones that were definitely correct.

    Agree with the rest, code is working and no error occurs. A bit strange though, because according to me the values are not well formed XML.

    From BOL

    Well-formed XML and the xml Data Type

    The xml data type implements the ISO standard xml data type. Therefore, it can store well-formed XML version 1.0 documents and also so-called XML content fragments with text nodes and an arbitrary number of top-level elements in an untyped XML column. The system checks that the data is well-formed, does not require the column to be bound to XML schemas, and rejects data that is not well-formed in the extended sense. This is true also of untyped XML variables and parameters.

Viewing 15 posts - 1 through 15 (of 37 total)

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