asked on

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')

Ryan Chong

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

ASKER

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

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PAGANED

ASKER

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

ASKER

a good lesson on how to deal with multiple namespaces inside an XML file

Ryan Chong

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