Querying a document in SQL Server 2012 stored as xml

Question

I have been trying to learn Open XML, and I successfully generated a free text 2007 .docx file in Visual Studio c#. After that I saved the file in the .xml format in Word and then Imported the .xml file into sql server 2012 into a table called XML to a column called xml: here is the code for the table:

CREATE TABLE [dbo].[XML](
    [XML_ID] [int] IDENTITY(1,1) NOT NULL,
    [XML] [xml] NOT NULL,
 CONSTRAINT [PK_XML] PRIMARY KEY CLUSTERED 
(
    [XML_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Here is the insert I ran

INSERT INTO XML(XML)
SELECT * FROM OPENROWSET(
   BULK 'C:\Users\bbt2d\Desktop\Document.xml',
   SINGLE_BLOB) AS x;

When I ran a select * the xml file showed up, but when I tried querying some of the data from the paragraphs I wasn't getting anywhere.

Here is a partial snipper of the xml that is stored in sql server: Was wondering if I went about the process wrong, or how would you query out text from the document.

?mso-application progid="Word.Document"?>

  
    
      
        
        
        
      
    
  
  
    
      
        
        
        
        
        
        
        
        
        
        
      
    
  
  
    
      
        
          
            
              
              
              
              
                
                
                
              
            
            
              
                
                
                
              
              School District of Marshfield
            
          
          
            
              
              
              
              
              
                
                
                
                
                
              
            
          
          
            
              
              
              
              
              
              
                
                
                
                
                
              
            
            
              
                
                
                
                
                
              
              Exploring French
            
            
              
                
                
                
                
                
              
               & Spanish

marc_s · Accepted Answer

The XML you posted isn't complete - assuming your table's XML is complete, you can try something like this (BTW: as a side-note: I would recommend to never use reserved words for your columns; don't call your column XML - that's a reserved keyword; use XmlDoc or something like that....)

-- define the two XML namespaces we need to use to get to the data in question
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/office/2006/xmlPackage' as pkg, 
                     'http://schemas.openxmlformats.org/wordprocessingml/2006/main' as w)
select
    XmlContent.value('(//w:t)[2]', 'varchar(100)')
FROM dbo.YourTable
where ....(some condition here).....

The really interesting part is finding out the proper XPath expression to get to your data. Unfortunately, the text you're interested in doesn't seem to be in a specifically marked paragraph, e.g. the and tags surrounding it don't have any specific attributes you can check for - so all you can do is figure out that it's the second node in that document that you're interested in, and get to its text that way.

Querying a document in SQL Server 2012 stored as xml

Answers (2)

Related Questions