Import field in XML using SQL not working

Question

i've been trying to import the field GivenName in my example XML but for some reason it's not working, i've been using the following SQL query, i think i'm using the correct field and the correct nodes but i'm not 100% sure about the XMLNameSpaces

Thank you very much in advance for your help

This is the example SQL Query i'm using:

DECLARE @xml XML = (SELECT [Xml] FROM ExampleTable)

 ;WITH XMLNAMESPACES (DEFAULT 'http://www.opentravel.org/OTA/2003/05','http://www.w3.org/2003/05/soap-envelope' )

select FirstName = ProfileInfo.value('Profiles[1]/ProfileInfo[1]/Profile[1]/Customer[1]/PersonName[1]/@GivenName', 'nvarchar(255)')
    FROM @xml.nodes('Envelope/Body/OTA_Example/Info/Infos/ResUser') as T1(Profiles) 
      outer apply T1.Profiles.nodes('ResUser2') as T2(ProfileInfo)

This is the example XML i'm using for the import:

<Envelope xmlns="http://www.w3.org/2003/05/soap-envelope">
  <soap2:Header xmlns:htng="http://htng.org/1.3/Header/" xmlns:wsa="http://www.w3.org/2005/08/addressing" 
  xmlns:wss="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" 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:htnga="http://htng.org/PWSWG/2007/02/AsyncHeaders" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
  xmlns:soap2="http://www.w3.org/2003/05/soap-envelope">
    <wsa:Action>Example</wsa:Action>
    <wsa:ReplyTo>
      <wsa:Address>Example2</wsa:Address>
    </wsa:ReplyTo>
    <htnga:ReplyTo>
      <wsa:Address>Example3</wsa:Address>
    </htnga:ReplyTo>
    <wsa:MessageID>123</wsa:MessageID>
    </soap2:Header>
  <Body>
    <OTA_Example xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns="http://www.opentravel.org/OTA/2003/05" Version="5.000" >
      <Info>
        <Infos CreateDateTime="2021-09-20T06:52:40" CreatorID="User">
          <UniqueID Type="1" ID="12341251" />
           <ResUser>
            <ResUser2 ResGuestRPH="1" PrimaryIndicator="true">
              <Profiles>
                <ProfileInfo>
                    <Profile ProfileType="1">
                    <Customer>
                      <PersonName>
                        <NamePrefix>Mr.</NamePrefix>
                        <GivenName>FirstnameTest</GivenName>
                        <Surname>LastnameTest</Surname>
                      </PersonName>
                      </Customer>
                  </Profile>
                </ProfileInfo>
              </Profiles>
            </ResUser2>
          </ResUser>
         </Infos>
      </Info>
    </OTA_Example>
  </Body>
</Envelope>

How many ResUser nodes do you expect (or any other of its parent nodes)? — Charlieface
– Charlieface, Commented Sep 20, 2021 at 11:29

Charlieface · Accepted Answer · 2021-09-20 11:38:04Z

1

GivenName is not an attribute, so you shouldn't use @ for it.

It's unclear why you needed .nodes, it is only needed if there were multiple nodes that needed breaking out into separate rows

You can also select straight out of ExampleTable, you do not need to store it in a variable.

;WITH XMLNAMESPACES (
   'http://www.w3.org/2003/05/soap-envelope' AS soap,
   DEFAULT 'http://www.opentravel.org/OTA/2003/05')

select FirstName = [XML].value('(soap:Envelope/soap:Body/OTA_Example/Info/Infos/ResUser/ResUser2/Profiles/ProfileInfo/Profile/Customer/PersonName/GivenName/text())[1]', 'nvarchar(255)')
    FROM ExampleTable

db<>fiddle

answered Sep 20, 2021 at 11:38

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BrianF. Over a year ago

Thank you for your answer Charlieface, Ah yeah you're right about the atrribute, i missed that. The example i posted is part of a much bigger XML (there are parts where there are multiple nodes) and it's necessary to use .nodes there.

BrianF. Over a year ago

but i was now able to adapt my Query using your solution and help and now it's working :)

BrianF. Over a year ago

just did it (can't upvote it because i don't have enough reputation points yet but i clicked on the check mark), Thanks again

Collectives™ on Stack Overflow

Import field in XML using SQL not working

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related