Import SOAP XML response into Database Table using SQL Server

Hi fredo1094,

We need to perform an XQuery against the XML and returns a value of SQL type. This method value returns a scalar value.

For more details refer https://learn.microsoft.com/en-us/sql/t-sql/xml/value-method-xml-data-type?view=sql-server-ver16.

Then specify path in the nodes() clause.

Refer below query.

SQL

CREATE TABLE #Customers([Id] INT, [Name] VARCHAR(100), [Country] VARCHAR(50))

DECLARE @XML XML = '<?xml version="1.0" encoding="UTF-8"?>
					<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
						<soapenv:Body>
							<Customer xmlns="urn:Customers:Customer">
								<CustomerId>1</CustomerId>
								<Name>Mudassar Khan</Name>
								<Country>India</Country>
							</Customer>
						</soapenv:Body>
					</soapenv:Envelope>';

;WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' as [soap], default 'urn:Customers:Customer')
INSERT INTO #Customers
SELECT T.X.value('(CustomerId/text())[1]', 'INT') as Id,
       T.X.value('(Name/text())[1]', 'VARCHAR(100)') as Name,
       T.X.value('(Country/text())[1]', 'VARCHAR(50)') as Country
FROM @XML.nodes('/soap:Envelope/soap:Body/Customer') as T(X)

SELECT * FROM #Customers
DROP TABLE #Customers

Screenshot