none
Help - Shredding XML

    Question

  • I have a very small XML file (it's
    a sample file, i'm just trying to learn how to do this right now) which
    i'm inserting into a table.  Once there, i'm trying to query the file
    but get an empty result set.  I've tried using Xquery and OpenXML but
    get the same results with each method.

    I really don't understand
    why this doesn't return  any results.  All i'm trying to do is get the
    book titles and the prices.  I get the columns to return, but no data.

    Why?

    EDIT - I'm having a very difficult time trying to put XML code in here, giving me errors.  I'm posting it as-is outside of the code block.  Here's the XML file i'm using:

    <bookstore>
      <book>
        <title lang="eng">Harry Potter</title>
        <price>29.99</price>
      </book>
      <book>
        <title lang="eng">Learning XML</title>
        <price>39.95</price>
      </book>
    </bookstore>

     

     


    
    

    Here's the code i'm using to insert the file into a table:

    --CREATE TABLE TO STORE XML FILES
    CREATE TABLE XMLTest
    (
    	ID	INT IDENTITY PRIMARY KEY
    	,XMLFIle	XML
    	,LoadDate	DATETIME
    )
    GO
    
    --BULK INSERT THE XML FILE(s)
    INSERT INTO XMLTest (XMLFile, LoadDate)
    	SELECT CONVERT(XML, BulkColumn) as BulkColumn, GetDate()
    	FROM OPENROWSET(BULK 'C:\XML\Books.xml', SINGLE_BLOB) as X
    GO
    
    --SELECT THE FILES FROM THE TABLE TO ENSURE INSERT WORKED
    SELECT * FROM XMLTest

    Here's my query in OpenXML:

    DECLARE  @XML  as XML
    		,@HDoc as INT
    		,@SQL  as VARCHAR(MAX)
    
    SELECT @XML = XMLFile FROM XMLTest
    
    
    EXEC sp_xml_preparedocument @HDoc OUTPUT, @XML
    
    SELECT *
    FROM OPENXML(@HDoc, 'Bookstore/Book/Title/Price')
    WITH
    (
    	Title	[VARCHAR](50)	'Title'
    	,Price	[DECIMAL](5,2)	'Price'
    )
    
    EXEC sp_xml_removedocument @HDoc
    GO
    Can
    someone please explain how I can get values the book title and price?  I
    have a real work project coming up in which I have to shred a larger
    XML file, however I want to get the hang of doing this on a smaller file
    first.

    What am I doing wrong?

    Thanks!!

            
        
       




    Thursday, July 25, 2013 11:07 PM

Answers

  • XML is always case-sensitive.

    declare @xml xml = '<bookstore>
      <book>
        <title lang="eng">Harry Potter</title>
        <price>29.99</price>
      </book>
      <book>
        <title lang="eng">Learning XML</title>
        <price>39.95</price>
      </book>
    </bookstore>'
    
    --using xml.nodes
    select T.books.value('title[1]', 'varchar(50)') Title,
           T.books.value('price[1]', 'decimal(5,2)') Price
    from @xml.nodes('/bookstore/book') T(books)
    
    --using OPENXML
    declare @HDoc int
    EXEC sp_xml_preparedocument @HDoc OUTPUT, @XML
    
    SELECT *
    FROM OPENXML(@HDoc, '/bookstore/book')
    WITH
    (
    	Title	[VARCHAR](50)	'title'
    	,Price	[DECIMAL](5,2)	'price'
    )
    
    EXEC sp_xml_removedocument @HDoc
    


    David


    David http://blogs.msdn.com/b/dbrowne/


    Thursday, July 25, 2013 11:14 PM

All replies

  • XML is always case-sensitive.

    declare @xml xml = '<bookstore>
      <book>
        <title lang="eng">Harry Potter</title>
        <price>29.99</price>
      </book>
      <book>
        <title lang="eng">Learning XML</title>
        <price>39.95</price>
      </book>
    </bookstore>'
    
    --using xml.nodes
    select T.books.value('title[1]', 'varchar(50)') Title,
           T.books.value('price[1]', 'decimal(5,2)') Price
    from @xml.nodes('/bookstore/book') T(books)
    
    --using OPENXML
    declare @HDoc int
    EXEC sp_xml_preparedocument @HDoc OUTPUT, @XML
    
    SELECT *
    FROM OPENXML(@HDoc, '/bookstore/book')
    WITH
    (
    	Title	[VARCHAR](50)	'title'
    	,Price	[DECIMAL](5,2)	'price'
    )
    
    EXEC sp_xml_removedocument @HDoc
    


    David


    David http://blogs.msdn.com/b/dbrowne/


    Thursday, July 25, 2013 11:14 PM
  • Ah, thanks for the answer and the tip. 

    Still doesn't seem to work, though.  This time i'm simply getting the column names for title and price but the result set is NULL.

    Any other suggestions as to what I could try/what might have gone wrong?

    Thursday, July 25, 2013 11:27 PM
  • >Any other suggestions as to what I could try/what might have gone wrong?

    I added to the code.  It is now self-contained and has both xml.nodes() and OPENXML examples.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, July 25, 2013 11:31 PM
  • Excellent, thank you, that does it. 

    The takeaway here is that XQuery must be done on an XML type (such as a XML variable) and also use singletons.  I'll have to play with this a bit more to fully understand it. 

    Right now it looks as though OpenXML might be easier for me, but we shall see.

    Thanks!!

    Thursday, July 25, 2013 11:45 PM
  • >The takeaway here is that XQuery must be done on an XML type (such as a XML variable) and also use singletons. 

    You can use XQuery on a table with an XML column by using CROSS APPLY.  SEE BOL for examples.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, July 25, 2013 11:57 PM