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)', 'varchar(200)') AS ProductName,
S.Node.value('(../@ID)', '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.