SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Workshop VI - Typed XML and SCHEMA Collection


XML Workshop VI - Typed XML and SCHEMA Collection

Author
Message
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2600 Visits: 2523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3118.asp

.
mojo-168709
mojo-168709
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1113 Visits: 831
Very informative. Keep on going!
bdba
bdba
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 338
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.
Attachments
MyXML_Schema.txt (32 views, 1.00 KB)
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2600 Visits: 2523
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
*/



.
bdba
bdba
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 338
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.
SwePeso
SwePeso
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9293 Visits: 3433
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"
yazalpizar_
yazalpizar_
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 626
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search