0

I have a xml File, which has a strucure like this:

 <Material>
     <MaterialNumber>7654321</MaterialNumber>
     <Carton>
        <IDType>qwer</IDType>
        <EncodingType>asdf</EncodingType
     </Carton>
 </Material>

I want to import materialnumber as well as idtype ... how can I import when there are two different layers? I only want to switch @Attribute. Thank you very much!

SET @Attribute = 'Carton/IDType'
SELECT
a.b.value('*[local-name() = sql:variable("@Attribute")][1]','varchar(1000)') AS blub
FROM @myxml.nodes('//Material') a(b)
Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
user2769104
  • 1
  • 1

1 Answers1

1
select
    @myxml.value('(Material/MaterialNumber/text())[1]', 'varchar(max)') as MaterialNumber,
    @myxml.value('(Material/Carton/IDType/text())[1]', 'varchar(max)') as IDType

sql fiddle demo

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197