TSQL XML Namespaces

PAGANED
PAGANED used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Ryan ChongSoftware Team Lead

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

PAGANED

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
Software Team Lead
Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Ryan ChongSoftware Team Lead
Commented:
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

PAGANED

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!
PAGANED

Author

Commented:
a good lesson on how to deal with multiple namespaces inside an XML file
Ryan ChongSoftware Team Lead

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial