We help IT Professionals succeed at work.

TSQL XML Namespaces

PAGANED
PAGANED asked
on
738 Views
Last Modified: 2017-03-19
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')
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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

Author

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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!

Author

Commented:
a good lesson on how to deal with multiple namespaces inside an XML file
CERTIFIED EXPERT

Commented:
Glad could make some helps. We are learning new things in life cheers

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions