XML Workshop VI - Typed XML and SCHEMA Collection

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jacob Sebastian » XML Workshop VI - Typed XML and SCHEMA...

XML Workshop VI - Typed XML and SCHEMA Collection

Rate Topic

Display Mode

Topic Options

Author

Message

jacob sebastian

Posted Monday, July 16, 2007 5:22 PM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

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

.

Post #382149

mojo-168709

Posted Thursday, September 06, 2007 3:29 PM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 10:46 AM
Points: 911, Visits: 831

Very informative. Keep on going!

Post #397090

bdba

Posted Tuesday, March 03, 2009 6:17 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:15 PM
Points: 112, Visits: 319

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.

Post Attachments

MyXML_Schema.txt (6 views, 1.79 KB)

Post #667772

jacob sebastian

Posted Tuesday, March 03, 2009 10:33 PM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

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
*/

Post #667842

bdba

Posted Friday, March 06, 2009 11:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:15 PM
Points: 112, Visits: 319

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.

Post #670502

SwePeso

Posted Saturday, July 04, 2009 2:16 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:26 PM
Points: 2,359, Visits: 3,293

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"

Post #747178

yazalpizar_

Posted Tuesday, December 27, 2011 1:59 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:02 AM
Points: 139, Visits: 293

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

Post #1226805

« Prev Topic | Next Topic »

Permissions