Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Workshop VI - Typed XML and SCHEMA Collection Expand / Collapse
Author
Message
Posted Monday, July 16, 2007 5:22 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3118.asp

.
Post #382149
Posted Thursday, September 6, 2007 3:29 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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
Posted Tuesday, March 3, 2009 6:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 (8 views, 1.79 KB)
Post #667772
Posted Tuesday, March 3, 2009 10:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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
Posted Friday, March 6, 2009 11:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Saturday, July 4, 2009 2:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:22 AM
Points: 2,393, Visits: 3,398
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
Posted Tuesday, December 27, 2011 1:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 142, Visits: 429
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 »

Add to briefcase

Permissions Expand / Collapse