Avatar of PAGANED
PAGANED
Flag for United States of America 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')
Microsoft SQL ServerXML

Avatar of undefined
Last Comment
Ryan Chong

8/22/2022 - Mon
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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