Share via

How to parse SOAP XML in SQL Server and show as table

diego diaz 1 Reputation point
2024-05-23T02:00:53.51+00:00

DECLARE @xml XML ='<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">

<soapenv:Header />

soapenv:Body

<ns0:GetListResponse xmlns:ns0="urn:Consultaurartmasiva1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <ns0:getListValues>

    <ns0:ID_de_petición>000000000250066</ns0:ID_de_petición>

    <ns0:Remitente />

    <ns0:Estado>New</ns0:Estado>

    <ns0:Descripción_breve />

    <ns0:Nro_Sigest>000</ns0:Nro_Sigest>

    <ns0:Mano_de_Obra>C</ns0:Mano_de_Obra>

    <ns0:Dirección_del_Trabajo>alle Rondeau casi Salta de Ciudad</ns0:Dirección_del_Trabajo>

    <ns0:Contratista>RETESAR</ns0:Contratista>

    <ns0:Llave>NO</ns0:Llave>

    <ns0:Submit_Date>2023-12-11T13:22:43-03:00</ns0:Submit_Date>

    <ns0:EstadoINC>Assigned</ns0:EstadoINC>

    <ns0:MasivaPar_Secundario_Hasta>3500</ns0:MasivaPar_Secundario_Hasta>

    <ns0:MasivaPar_Secundario_Desde>3301</ns0:MasivaPar_Secundario_Desde>

    <ns0:MasivaCentral_Armario>272</ns0:MasivaCentral_Armario>

    <ns0:MasivaArmario>8157</ns0:MasivaArmario>

    <ns0:Garantía>NO</ns0:Garantía>

    <ns0:Tipo_Masiva>Cable-Red Secundaria</ns0:Tipo_Masiva>

    <ns0:Categorization_Tier_1>INCIDENCIA MASIVA</ns0:Categorization_Tier_1>

    <ns0:Categorization_Tier_2>PAR SECUNDARIO</ns0:Categorization_Tier_2>

    <ns0:Categorization_Tier_3>ROBO</ns0:Categorization_Tier_3>

    <ns0:Incident_Number>INC000129821691</ns0:Incident_Number>

    <ns0:Priority>Critical</ns0:Priority>

    <ns0:Reported_Date>2023-12-11T13:22:01-03:00</ns0:Reported_Date>

    <ns0:Required_Resolution_DateTime>2023-12-14T00:00:00-03:00</ns0:Required_Resolution_DateTime>

  </ns0:getListValues>

</ns0:GetListResponse>

</soapenv:Body>

</soapenv:Envelope>'

;WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' AS soapenv

, 'http://www.w3.org/2001/XMLSchema' AS ns

, DEFAULT 'http://www.w3.org/2001/XMLSchema-instance')

SELECT c.value('(Tipo_Masiva/text())[1]', 'VARCHAR(100)') AS [key]

FROM @xml.nodes('/soapenv:Envelope/soapenv:Body/ns0:GetListResponse/ns0:getListValues') AS t(c);

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,067 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. LiHongMSFT-4306 24,361 Reputation points
    2024-05-23T07:03:39.31+00:00

    Hi @diego diaz

    How about modify the ns to ns0?

    'http://www.w3.org/2001/XMLSchema' AS ns0

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments
  2. Viorel 113.7K Reputation points
    2024-05-23T10:06:19.7833333+00:00

    Try to fix and to add the namespace:

    ;
with xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' as soapenv, 'urn:Consultaurartmasiva1' as ns0)
select c.value('ns0:Tipo_Masiva[1]', 'varchar(100)') as [key]
from @xml.nodes('/soapenv:Envelope/soapenv:Body/ns0:GetListResponse/ns0:getListValues') as t(c)
    0 comments
  3. Yitzhak Khabinsky 25,381 Reputation points
    2024-05-23T10:20:40.55+00:00

    Hi @diego diaz,

    Here is a full solution for you.

    DECLARE @xml XML =
N'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
	<soapenv:Header/>
	<soapenv:Body>
		<ns0:GetListResponse xmlns:ns0="urn:Consultaurartmasiva1"
		                     xmlns:xsd="http://www.w3.org/2001/XMLSchema"
		                     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
			<ns0:getListValues>
				<ns0:ID_de_petición>000000000250066</ns0:ID_de_petición>
				<ns0:Remitente/>
				<ns0:Estado>New</ns0:Estado>
				<ns0:Descripción_breve/>
				<ns0:Nro_Sigest>000</ns0:Nro_Sigest>
				<ns0:Mano_de_Obra>C</ns0:Mano_de_Obra>
				<ns0:Dirección_del_Trabajo>alle Rondeau casi Salta de Ciudad</ns0:Dirección_del_Trabajo>
				<ns0:Contratista>RETESAR</ns0:Contratista>
				<ns0:Llave>NO</ns0:Llave>
				<ns0:Submit_Date>2023-12-11T13:22:43-03:00</ns0:Submit_Date>
				<ns0:EstadoINC>Assigned</ns0:EstadoINC>
				<ns0:MasivaPar_Secundario_Hasta>3500</ns0:MasivaPar_Secundario_Hasta>
				<ns0:MasivaPar_Secundario_Desde>3301</ns0:MasivaPar_Secundario_Desde>
				<ns0:MasivaCentral_Armario>272</ns0:MasivaCentral_Armario>
				<ns0:MasivaArmario>8157</ns0:MasivaArmario>
				<ns0:Garantía>NO</ns0:Garantía>
				<ns0:Tipo_Masiva>Cable-Red Secundaria</ns0:Tipo_Masiva>
				<ns0:Categorization_Tier_1>INCIDENCIA MASIVA</ns0:Categorization_Tier_1>
				<ns0:Categorization_Tier_2>PAR SECUNDARIO</ns0:Categorization_Tier_2>
				<ns0:Categorization_Tier_3>ROBO</ns0:Categorization_Tier_3>
				<ns0:Incident_Number>INC000129821691</ns0:Incident_Number>
				<ns0:Priority>Critical</ns0:Priority>
				<ns0:Reported_Date>2023-12-11T13:22:01-03:00</ns0:Reported_Date>
				<ns0:Required_Resolution_DateTime>2023-12-14T00:00:00-03:00</ns0:Required_Resolution_DateTime>
			</ns0:getListValues>
		</ns0:GetListResponse>
	</soapenv:Body>
</soapenv:Envelope>';
  
with xmlnamespaces (DEFAULT 'http://schemas.xmlsoap.org/soap/envelope/', 'urn:Consultaurartmasiva1' as ns0)
select c.value('(ns0:Tipo_Masiva/text())[1]', 'varchar(100)') as [key]
from @xml.nodes('/Envelope/Body/ns0:GetListResponse/ns0:getListValues') as t(c);
    0 comments