Post reply

XML Workshop VI - Typed XML and SCHEMA Collection

  • July 16, 2007 at 5:22 pm

    #372858

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3118.asp

    .

  • September 6, 2007 at 3:29 pm

    #731987

    Very informative. Keep on going!

  • March 3, 2009 at 6:17 pm

    #952810

    Hi Jacob,

    Wondering if could give some feedback to my issue here.

    With SQL Server 2008...

    I have an XML document and created it's XSD schema using VSTF (Visual Studio) create schema tool.

    I am attaching the XML document and it's XSD schema created by VSTF here.

    I am trying to shred the values from the XML document into my table.

    If I do select by binding the XML doc to its schema I get the following error.

    However, I am able to run my select successfully if I don't bound the XML doc with its XSD schema.

    So I know that my Select statement using value() and nodes() method is correct.

    Msg 2389, Level 16, State 1, Line 23

    XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:anyAtomicType *'

    Below is my Select query using nodes() and value() method.

    SELECT

    S.Node.value('(../@Name)[1]', 'varchar(200)') AS ProductName,

    S.Node.value('(../@ID)[1]', 'int') AS ProductID,

    S.Node.value('@Keyname', 'varchar(200)') AS ItemName,

    S.Node.value('@Keyvalue', 'varchar(max)') AS ItemValue

    FROM @MyXML.nodes('//MyXML/Product/Item') S(Node);

    If I declare the XML doc by associating it to the XSD schema like you have done in this article, I get the above error, however, without association, it's succcessfully shreds my data.

    You can simply copy the content of the attachment to you SQL Management Query window and run it to see the error. If you run without bounding it to the schema, it will run fine.

  • March 3, 2009 at 10:33 pm

    #952870

    bdba,

    I altered the query a bit. Here is the new version

    SELECT

    p.value('@Name', 'varchar(15)') AS ProductName,

    p.value('@ID', 'int') AS ProductID,

    i.value('@Keyname', 'varchar(15)') AS ItemName,

    i.value('@Keyvalue', 'varchar(15)') AS ItemValue

    FROM @MyXML.nodes('/MyXML/Product') x(p)

    CROSS APPLY p.nodes('Item') y(i)

    /*

    ProductName ProductID ItemName ItemValue

    --------------- ----------- --------------- ---------------

    MyProduct 12354 MyItem MyItemValue

    MyProduct 12354 MyPrice MyPriceValue

    MyProduct1 1235456 MyItem1 MyItemValue1

    MyProduct1 1235456 MyPrice1 MyPriceValue1

    */

    .

  • March 6, 2009 at 11:20 am

    #955132

    Thanks Jacob,

    That works pretty well. Have used Cross Apply with XML and another table but within the same XML. This is pretty useful. Appreciate your feedback on this.

  • July 4, 2009 at 2:16 am

    #1019925

    Using the SEQUENCE option in the XSD forces you to have the elements in same order in the XML file.

    See http://www.w3schools.com/Schema/schema_complex_indicators.asp

    N 56°04'39.16"
    E 12°55'05.25"

  • December 27, 2011 at 1:59 am

    #1425609

    One less to go...very informative as previous ones

    But as on previous posts of this XML series, the links to the code are empty

Viewing 7 posts - 1 through 6 (of 6 total)

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