TSQL XML Namespaces

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')
LVL 1
PAGANEDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
your date:

<QuoteBy>2017-03 19T17:00:00</QuoteBy>

Open in new window


should be defined as:
<QuoteBy>2017-03-19T17:00:00</QuoteBy>

Open in new window


and then you may modify from this:

	
DECLARE @XML XML

SET @XML = CONVERT (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>
	');
	
WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS soap, 'http://www.papapa.com/webservices/' AS ns)
SELECT 
	c.value('(ns:GetRFQsResult/ns:RequestForQuote/ns:Posted)[1]', 'DATETIME') AS Posted,
	c.value('(ns:GetRFQsResult/ns:RequestForQuote/ns:QuoteBy)[1]', 'DATETIME') AS QuoteBy
	
FROM
@XML.nodes('/soap:Envelope/soap:Body/ns:GetRFQsResponse') t(c)

Open in new window

0
PAGANEDAuthor Commented:
Thank You for your reply
.
I have a lot of things built around : sp_xml_preparedocument
.
Do you have a method that utilizes the code I presented ?
.
XQUERY is SLOW !
.
OPENXML has the speed to open up these +3Mb XML files
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ok, try this:

source: (test.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>

Open in new window


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

-- Append to XML Temp Table

DECLARE @PATH VARCHAR(100)
SET @PATH = 'D:\yourPath\test.xml'

      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" xmlns:ns="http://www.papapa.com/webservices/"/>' 
                        
-- Get Child Nodes

      SELECT Posted,QuoteBy
      FROM OPENXML(@hDoc, '/soap:Envelope/soap:Body/ns:GetRFQsResponse/ns:GetRFQsResult/ns:RequestForQuote', 1)
      WITH
            ([Posted] datetime 'ns:Posted',                  
            [QuoteBy] datetime 'ns:QuoteBy') 
        
     EXEC sp_xml_removedocument @hDoc

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
this should work as well, if the XML is loaded from a char

	
DECLARE @XML XML

SET @XML = CONVERT (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>
	');
	
	
DECLARE @hDoc AS INT
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" xmlns:ns="http://www.papapa.com/webservices/"/>' 
                        
SELECT Posted,QuoteBy
FROM OPENXML(@hDoc, '/soap:Envelope/soap:Body/ns:GetRFQsResponse/ns:GetRFQsResult/ns:RequestForQuote', 1)
WITH
    ([Posted] datetime 'ns:Posted',                  
    [QuoteBy] datetime 'ns:QuoteBy') 

    
EXEC sp_xml_removedocument @hDoc  

Open in new window

0
PAGANEDAuthor Commented:
AWESOME !  (it's times like these that you remember there is Magic !)
.
So ...
1.    I didnt include the lower namespace with the proc sp_xml_preparedocument : xmlns:ns="http://www.papapa.com/webservices/"

2.   I didn't realize that I needed to use the "NS" to precede the group :    OPENXML(@hDoc, '/soap:Envelope/soap:Body/ns:GetRFQsResponse/ns:GetRFQsResult/ns:RequestForQuote', 1)

3.   I then also needed to keep the NS preceding text for each element I was grabbing :  'ns:Posted'
.
.
Good lesson for anyone dealing with namespaces in their XML
I was rewriting the XML out by removing the namespace header lines and footer elements, now I don't have to take that unnecessary step

Thank You!
0
PAGANEDAuthor Commented:
a good lesson on how to deal with multiple namespaces inside an XML file
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Glad could make some helps. We are learning new things in life cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.