Home Forums Programming XML XML Type XML Attribute query RE: XML Type XML Attribute query

  • It's hard to make an intelligent recommendation without a couple of representative samples. If the elements are NAMED the same, but just in different parts of the structure - you might not need to do anything custom, just do an XML scan for the names.

    declare @xml xml = '<root>

    <TransType tc="NNN"/>

    <fun>

    <insDate>2017-10-15</insDate>

    <morefun>

    <insid>777</insid>

    </morefun>

    </fun>

    </root>';

    select x.value('(//TransType/@tc)[1]','varchar(10)') TransactionType, --note: the // means scan anywhere for this element

    x.value('(//insDate)[1]','datetime') InsertDate,

    x.value('(//insid)[1]','integer') InsertID

    from @xml.nodes('*[./TransType[1]/@tc="NNN"]' ) a(x)

    If not - it can get messy pretty quick. At that point unless the stuff is VERY simple - I'd consider a two pass approach:

    1.pull the transtype

    2.have an If statement determine which query to use to pull the other elements.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?