troubleshooting Question

TSQL XML Namespaces

Avatar of PAGANED
PAGANEDFlag for United States of America asked on
Microsoft SQL ServerXML
7 Comments2 Solutions765 ViewsLast Modified:
I am trying to get the data from an XML file that has Namespaces
.
I can't seem to get the syntax correct .. I only return Blank Fields
.
If I edit the XML file removing the soap:envelope, soap:body, and getrfqresponse  .. then I can get to the data
the Namespace declaration is messing me up
the soap:body & the child namespace in GetRFQsResponse are problems I am having including with the namespace
.
.
.
XML
..............................................................................................
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
          <soap:Body>
                    <GetRFQsResponse xmlns="http://www.papapa.com/webservices/">
                              <GetRFQsResult>
                                        <RequestForQuote>
                                                  <Posted>2017-03-14T11:53:55.677</Posted>
                                                  <QuoteBy>2017-03 19T17:00:00</QuoteBy>
                                        </RequestForQuote>
                              </GetRFQsResult>
                    </GetRFQsResponse>
            </soap:Body>
 </soap:Envelope>
................................................................................

TSQL
................................................................................
-- Create Temp Table for XML Data
      CREATE  TABLE #XMLtable
      (Id INT IDENTITY PRIMARY KEY,
      XMLData XML)

-- Append to XML Temp Table
        DECLARE @SQLStatement nvarchar(500)
      SET @SQLStatement = 'SELECT CONVERT(XML, BulkColumn) AS BulkColumn FROM OPENROWSET(BULK ''' + @PATH + ''' , SINGLE_BLOB) AS x;'
      INSERT INTO #XMLtable(XMLData)
      EXEC(@SQLStatement)
      
--Declare XML field type
      DECLARE @XML AS XML, @hDoc AS INT
      SELECT @XML = XMLData FROM #XMLtable
      DROP Table #XMLtable

-- EXECUTE Stored Proc
      EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML,
            '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />'
                        
-- Get Child Nodes
       SELECT Posted,QuoteBy
      FROM OPENXML(@hDoc, 'GetRFQsResult/RequestForQuote')
      WITH
            ([Posted] datetime 'Posted',                  
            [QuoteBy] datetime 'QuoteBy')
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros