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:5
5.677</Pos
ted>
<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/RequestForQ
uote')
WITH
([Posted] datetime 'Posted',
[QuoteBy] datetime 'QuoteBy')
Open in new window
should be defined as:
Open in new window
and then you may modify from this:
Open in new window