XML Workshop VI - Typed XML and SCHEMA Collection

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


  • Very informative. Keep on going!

  • 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.


    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.

  • bdba,

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


    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



  • 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.

  • 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"

  • 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