XML Type XML Attribute query

Question

XML Type XML Attribute query

jharvey6 76850

Old Hand

Points: 381
More actions
August 30, 2016 at 7:33 am

#100076

I have a small data table that store XML files of a certain standard design.
There are three columns [Index_ID] and IDENTITY seeded index, InsertDate, and XML_File of XML datatype.
Within the XML file I have an element with an attribute that is deterministic to construction of the XML file
Element is <TransType tc="NNN" />the attribute can be various 3 digit numbers, obviously as a string here.
How can I query the table for the Index_ID and InsertDate based on the value of this element attribute with the XML?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

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?

jharvey6 76850 Old Hand Points: 381 More actions · Answer 2

Thanks Matt. I'll give that a try. The Transtype is in the same location just inside the envelope. So, I can scan the element pretty easily. Where I got hung up was returning the attribute value. When the element was defined as <TransType tc=111 />.

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 3

jharvey6 76850 (8/30/2016)
I have a small data table that store XML files of a certain standard design.
There are three columns [Index_ID] and IDENTITY seeded index, InsertDate, and XML_File of XML datatype.
Within the XML file I have an element with an attribute that is deterministic to construction of the XML file
Element is <TransType tc="NNN" />the attribute can be various 3 digit numbers, obviously as a string here.
How can I query the table for the Index_ID and InsertDate based on the value of this element attribute with the XML?

Can you post an example of the XML?

😎

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

jharvey6 76850 (8/30/2016)
Thanks Matt. I'll give that a try. The Transtype is in the same location just inside the envelope. So, I can scan the element pretty easily. Where I got hung up was returning the attribute value. When the element was defined as <TransType tc=111 />.

attributes are retrieved using the @ in front of the name. If you notice - I gave you an example of that in there

----------------------------------------------------------------------------------
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?