none
How can I get value of XML first element and pivot row to column and second element as row? RRS feed

  • Question

  • Not sure if I'm asking correctly, but I keep getting nulls.

    My XML (soap env results)

    <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" xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
       <soap:Header>
          <wsa:Action>RetrieveResponse</wsa:Action>
          <wsa:MessageID>urn:uuid:a7c370f1-a01d-430f-ad01-31af84c60b6c</wsa:MessageID>
          <wsa:RelatesTo>urn:uuid:0f2463ae-1f7a-4734-a48b-188e2e483ce9</wsa:RelatesTo>
          <wsa:To>http://schemas.xmlsoap.org/ws/2004/08/addressing/role/anonymous</wsa:To>
          <wsse:Security>
             <wsu:Timestamp wsu:Id="Timestamp-1513b195-3e75-47dd-8c58-90880ce65a2b">
                <wsu:Created>2020-02-12T20:25:27Z</wsu:Created>
                <wsu:Expires>2020-02-12T20:30:27Z</wsu:Expires>
             </wsu:Timestamp>
          </wsse:Security>
       </soap:Header>
       <soap:Body>
          <RetrieveResponseMsg xmlns="http://someurl.xsd">
             <OverallStatus>OK</OverallStatus>
             <RequestID>268aa394</RequestID>
             <Results xsi:type="DataExtensionObject">
                <PartnerKey xsi:nil="true"/>
                <ObjectID xsi:nil="true"/>
                <Type>DataExtensionObject</Type>
                <Properties>
                   <Property>
                      <Name>Prospect_ID</Name>
                      <Value>99991234</Value>
                   </Property>
                   <Property>
                      <Name>Source_ID</Name>
                      <Value>Manual</Value>
                   </Property>
                   <Property>
                      <Name>First_Name</Name>
                      <Value>Mickey</Value>
                   </Property>
                   <Property>
                      <Name>Last_Name</Name>
                      <Value>Mouse</Value>
                   </Property>
                  </Properties>
             </Results>
             <Results xsi:type="DataExtensionObject">
                <PartnerKey xsi:nil="true"/>
                <ObjectID xsi:nil="true"/>
                <Type>DataExtensionObject</Type>
                <Properties>
                   <Property>
                      <Name>Prospect_ID</Name>
                      <Value>Bobby99</Value>
                   </Property>
                   <Property>
                      <Name>Source_ID</Name>
                      <Value>BobbyA</Value>
                   </Property>
                   <Property>
                      <Name>First_Name</Name>
                      <Value>Bobby</Value>
                   </Property>
                   <Property>
                      <Name>Last_Name</Name>
                      <Value>Jones</Value>
                   </Property>
                </Properties>
             </Results>
             <Results xsi:type="DataExtensionObject">
                <PartnerKey xsi:nil="true"/>
                <ObjectID xsi:nil="true"/>
                <Type>DataExtensionObject</Type>
                <Properties>
                   <Property>
                      <Name>Prospect_ID</Name>
                      <Value>Sam99</Value>
                   </Property>
                   <Property>
                      <Name>Source_ID</Name>
                      <Value>SamA</Value>
                   </Property>
                   <Property>
                      <Name>First_Name</Name>
                      <Value>Sam</Value>
                   </Property>
                   <Property>
                      <Name>Last_Name</Name>
                      <Value>Sneed</Value>
                   </Property>
                </Properties>
             </Results>
          </RetrieveResponseMsg>
       </soap:Body>
    </soap:Envelope>

    I have the XML stored in a table:

    Create Table MCAPISoapEnvelope
    ( ID INT NOT NULL,
    Envelope XML NOT NULL)

    Trying to get the following data to export to a new table.

    ID Prospect_ID Source_ID First_Name Last_Name
    1 99991234 Manual Mickey Mouse
    2 Bobby99 BobbyA Bobby Jones
    3 Sam99 SamA Sam Sneed


    Here is the code I have but keep getting null on first column, haven't even tried to pivot.

    select 

    mca.Id,
    m.c.value('Name[1]','varchar(55)') as ProspectID,
    m.c.value('Value[1]','varchar(55)') as SourceID
    from MCAPISoapEnvelope mca
    outer apply mca.Envelope.nodes('Envelope/Body/RetrieveResponseMsg/Result[1]/Properties[1]') as m(c)

    It seems is not recognizing my nodes. Not an expert but this xml is a bit of a challenge.


    • Edited by wturcios Friday, February 14, 2020 4:19 PM
    Friday, February 14, 2020 4:18 PM

Answers

  • Hi wturcios,

    Here is how to achieve what you need.

    SQL:

    -- DDL and sample data population, start
    DECLARE @MCAPISoapEnvelope TABLE ( ID INT PRIMARY KEY, Envelope XML NOT NULL);
    INSERT INTO @MCAPISoapEnvelope (ID, Envelope) VALUES
    (0, N'<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"
                   xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing"
                   xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
                   xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    	<soap:Header>
    		<wsa:Action>RetrieveResponse</wsa:Action>
    		<wsa:MessageID>urn:uuid:a7c370f1-a01d-430f-ad01-31af84c60b6c</wsa:MessageID>
    		<wsa:RelatesTo>urn:uuid:0f2463ae-1f7a-4734-a48b-188e2e483ce9</wsa:RelatesTo>
    		<wsa:To>http://schemas.xmlsoap.org/ws/2004/08/addressing/role/anonymous</wsa:To>
    		<wsse:Security>
    			<wsu:Timestamp wsu:Id="Timestamp-1513b195-3e75-47dd-8c58-90880ce65a2b">
    				<wsu:Created>2020-02-12T20:25:27Z</wsu:Created>
    				<wsu:Expires>2020-02-12T20:30:27Z</wsu:Expires>
    			</wsu:Timestamp>
    		</wsse:Security>
    	</soap:Header>
    	<soap:Body>
    		<RetrieveResponseMsg xmlns="http://someurl.xsd">
    			<OverallStatus>OK</OverallStatus>
    			<RequestID>268aa394</RequestID>
    			<Results xsi:type="DataExtensionObject">
    				<PartnerKey xsi:nil="true"/>
    				<ObjectID xsi:nil="true"/>
    				<Type>DataExtensionObject</Type>
    				<Properties>
    					<Property>
    						<Name>Prospect_ID</Name>
    						<Value>99991234</Value>
    					</Property>
    					<Property>
    						<Name>Source_ID</Name>
    						<Value>Manual</Value>
    					</Property>
    					<Property>
    						<Name>First_Name</Name>
    						<Value>Mickey</Value>
    					</Property>
    					<Property>
    						<Name>Last_Name</Name>
    						<Value>Mouse</Value>
    					</Property>
    				</Properties>
    			</Results>
    			<Results xsi:type="DataExtensionObject">
    				<PartnerKey xsi:nil="true"/>
    				<ObjectID xsi:nil="true"/>
    				<Type>DataExtensionObject</Type>
    				<Properties>
    					<Property>
    						<Name>Prospect_ID</Name>
    						<Value>Bobby99</Value>
    					</Property>
    					<Property>
    						<Name>Source_ID</Name>
    						<Value>BobbyA</Value>
    					</Property>
    					<Property>
    						<Name>First_Name</Name>
    						<Value>Bobby</Value>
    					</Property>
    					<Property>
    						<Name>Last_Name</Name>
    						<Value>Jones</Value>
    					</Property>
    				</Properties>
    			</Results>
    			<Results xsi:type="DataExtensionObject">
    				<PartnerKey xsi:nil="true"/>
    				<ObjectID xsi:nil="true"/>
    				<Type>DataExtensionObject</Type>
    				<Properties>
    					<Property>
    						<Name>Prospect_ID</Name>
    						<Value>Sam99</Value>
    					</Property>
    					<Property>
    						<Name>Source_ID</Name>
    						<Value>SamA</Value>
    					</Property>
    					<Property>
    						<Name>First_Name</Name>
    						<Value>Sam</Value>
    					</Property>
    					<Property>
    						<Name>Last_Name</Name>
    						<Value>Sneed</Value>
    					</Property>
    				</Properties>
    			</Results>
    		</RetrieveResponseMsg>
    	</soap:Body>
    </soap:Envelope>');
    -- DDL and sample data population, end
    
    ;WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' AS [soap],
    	DEFAULT 'http://someurl.xsd')
    SELECT tbl.ID
    	, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SeqNo
    	, c.value('(Property[Name="Prospect_ID"]/Value/text())[1]','VARCHAR(50)') AS ProspectID
    	, c.value('(Property[Name="Source_ID"]/Value/text())[1]','VARCHAR(50)') AS Source_ID
    	, c.value('(Property[Name="First_Name"]/Value/text())[1]','VARCHAR(50)') AS First_Name
    	, c.value('(Property[Name="Last_Name"]/Value/text())[1]','VARCHAR(50)') AS Last_Name
    FROM @MCAPISoapEnvelope AS tbl
    	CROSS APPLY tbl.Envelope.nodes('soap:Envelope/soap:Body/RetrieveResponseMsg/Results/Properties') as t(c);

    Output:

    ID	SeqNo	ProspectID	Source_ID	First_Name	Last_Name
    0	1	99991234	Manual	Mickey	Mouse
    0	2	Bobby99	        BobbyA	Bobby	Jones
    0	3	Sam99	        SamA	Sam	        Sneed



    • Edited by Yitzhak Khabinsky Friday, February 14, 2020 6:16 PM
    • Marked as answer by wturcios Saturday, February 15, 2020 3:36 AM
    Friday, February 14, 2020 6:14 PM
  • thank you Viorel much appreciate it.

    “If A is a success in life, then A equals x plus y plus z. Work is x; y is play; and z is keeping your mouth shut” A.E.

    • Marked as answer by wturcios Saturday, February 15, 2020 3:36 AM
    Saturday, February 15, 2020 3:35 AM

All replies

  • Check one of possible solutions:

    ;
    with xmlnamespaces (
    	'http://schemas.xmlsoap.org/soap/envelope/' as soap,
    	DEFAULT 'http://someurl.xsd'
    )
    select 
    	mca.Id,
    	m.c.value('Name[1]','varchar(55)') as ProspectID,
    	m.c.value('Value[1]','varchar(55)') as SourceID
    from MCAPISoapEnvelope mca
    outer apply mca.Envelope.nodes('soap:Envelope/soap:Body/RetrieveResponseMsg/Results/Properties/Property') as m(c)


    • Edited by Viorel_MVP Friday, February 14, 2020 5:42 PM
    Friday, February 14, 2020 5:42 PM
  • Hi wturcios,

    Here is how to achieve what you need.

    SQL:

    -- DDL and sample data population, start
    DECLARE @MCAPISoapEnvelope TABLE ( ID INT PRIMARY KEY, Envelope XML NOT NULL);
    INSERT INTO @MCAPISoapEnvelope (ID, Envelope) VALUES
    (0, N'<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"
                   xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing"
                   xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
                   xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    	<soap:Header>
    		<wsa:Action>RetrieveResponse</wsa:Action>
    		<wsa:MessageID>urn:uuid:a7c370f1-a01d-430f-ad01-31af84c60b6c</wsa:MessageID>
    		<wsa:RelatesTo>urn:uuid:0f2463ae-1f7a-4734-a48b-188e2e483ce9</wsa:RelatesTo>
    		<wsa:To>http://schemas.xmlsoap.org/ws/2004/08/addressing/role/anonymous</wsa:To>
    		<wsse:Security>
    			<wsu:Timestamp wsu:Id="Timestamp-1513b195-3e75-47dd-8c58-90880ce65a2b">
    				<wsu:Created>2020-02-12T20:25:27Z</wsu:Created>
    				<wsu:Expires>2020-02-12T20:30:27Z</wsu:Expires>
    			</wsu:Timestamp>
    		</wsse:Security>
    	</soap:Header>
    	<soap:Body>
    		<RetrieveResponseMsg xmlns="http://someurl.xsd">
    			<OverallStatus>OK</OverallStatus>
    			<RequestID>268aa394</RequestID>
    			<Results xsi:type="DataExtensionObject">
    				<PartnerKey xsi:nil="true"/>
    				<ObjectID xsi:nil="true"/>
    				<Type>DataExtensionObject</Type>
    				<Properties>
    					<Property>
    						<Name>Prospect_ID</Name>
    						<Value>99991234</Value>
    					</Property>
    					<Property>
    						<Name>Source_ID</Name>
    						<Value>Manual</Value>
    					</Property>
    					<Property>
    						<Name>First_Name</Name>
    						<Value>Mickey</Value>
    					</Property>
    					<Property>
    						<Name>Last_Name</Name>
    						<Value>Mouse</Value>
    					</Property>
    				</Properties>
    			</Results>
    			<Results xsi:type="DataExtensionObject">
    				<PartnerKey xsi:nil="true"/>
    				<ObjectID xsi:nil="true"/>
    				<Type>DataExtensionObject</Type>
    				<Properties>
    					<Property>
    						<Name>Prospect_ID</Name>
    						<Value>Bobby99</Value>
    					</Property>
    					<Property>
    						<Name>Source_ID</Name>
    						<Value>BobbyA</Value>
    					</Property>
    					<Property>
    						<Name>First_Name</Name>
    						<Value>Bobby</Value>
    					</Property>
    					<Property>
    						<Name>Last_Name</Name>
    						<Value>Jones</Value>
    					</Property>
    				</Properties>
    			</Results>
    			<Results xsi:type="DataExtensionObject">
    				<PartnerKey xsi:nil="true"/>
    				<ObjectID xsi:nil="true"/>
    				<Type>DataExtensionObject</Type>
    				<Properties>
    					<Property>
    						<Name>Prospect_ID</Name>
    						<Value>Sam99</Value>
    					</Property>
    					<Property>
    						<Name>Source_ID</Name>
    						<Value>SamA</Value>
    					</Property>
    					<Property>
    						<Name>First_Name</Name>
    						<Value>Sam</Value>
    					</Property>
    					<Property>
    						<Name>Last_Name</Name>
    						<Value>Sneed</Value>
    					</Property>
    				</Properties>
    			</Results>
    		</RetrieveResponseMsg>
    	</soap:Body>
    </soap:Envelope>');
    -- DDL and sample data population, end
    
    ;WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' AS [soap],
    	DEFAULT 'http://someurl.xsd')
    SELECT tbl.ID
    	, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SeqNo
    	, c.value('(Property[Name="Prospect_ID"]/Value/text())[1]','VARCHAR(50)') AS ProspectID
    	, c.value('(Property[Name="Source_ID"]/Value/text())[1]','VARCHAR(50)') AS Source_ID
    	, c.value('(Property[Name="First_Name"]/Value/text())[1]','VARCHAR(50)') AS First_Name
    	, c.value('(Property[Name="Last_Name"]/Value/text())[1]','VARCHAR(50)') AS Last_Name
    FROM @MCAPISoapEnvelope AS tbl
    	CROSS APPLY tbl.Envelope.nodes('soap:Envelope/soap:Body/RetrieveResponseMsg/Results/Properties') as t(c);

    Output:

    ID	SeqNo	ProspectID	Source_ID	First_Name	Last_Name
    0	1	99991234	Manual	Mickey	Mouse
    0	2	Bobby99	        BobbyA	Bobby	Jones
    0	3	Sam99	        SamA	Sam	        Sneed



    • Edited by Yitzhak Khabinsky Friday, February 14, 2020 6:16 PM
    • Marked as answer by wturcios Saturday, February 15, 2020 3:36 AM
    Friday, February 14, 2020 6:14 PM
  • Thanks Yitzhak much appreciate it. 

    I see and understand I was missing xmlnamespaces and makes sense to me now. Just so I can learn some more, what does DEFAULT do?

    Thanks again!


    “If A is a success in life, then A equals x plus y plus z. Work is x; y is play; and z is keeping your mouth shut” A.E.

    Saturday, February 15, 2020 3:35 AM
  • thank you Viorel much appreciate it.

    “If A is a success in life, then A equals x plus y plus z. Work is x; y is play; and z is keeping your mouth shut” A.E.

    • Marked as answer by wturcios Saturday, February 15, 2020 3:36 AM
    Saturday, February 15, 2020 3:35 AM
  • Thanks Yitzhak much appreciate it. 

    I see and understand I was missing xmlnamespaces and makes sense to me now. Just so I can learn some more, what does DEFAULT do?

    DEFAULT means that a namespace has no prefix. Check it out here: Handling Namespaces in XQuery

    Overall, there are few spots to pay attention to:

    1. Namespaces in the source XML, and how to handle them in T-SQL.
    2. Correct context via .nodes(...) XPath expression per row.
    3. Each column .value() XPath predicate expression to retrieve a correct column value.
    4. Use of the text() for the performance reasons.

    P.S. Please connect with me on the LinkedIn


    6 hours 41 minutes ago