Pair XML Attribute and Other Attributes with Parent Attribute Value SQL Server 2008

Question

I have xml

  <translation id="A">mi casa</translation>
  <translation id="B">
    <div style="width:100px; white-space:normal; line-height:13px;" onclick="DoSomething()">una gato</div>
  </translation>
  <translation id="C">ese</translation>

Looking to produce the following result set

attr   parent    value

style  B         width:100px; white-space:normal; line-height:13px;
onclck B         DoSomething()

Most everything in this XML document focuses on the value of the Translation ID. Many and no additional attributes can be present.

I have gotten this far:

select 
         pnd.value('(..//@id)[1]','nvarchar(200)') ID
            , pnd.value('(//div/@*) [1] ','nvarchar(max)') elem
            ,pnd.value('local-name(@*[1])','nvarchar(200)') as Attribute
            from @inputXML.nodes('/translation_collection/translation/*') pn(pnd)

Edit: Only gives me the first singleton, when looking for all. Could not get past the bloat on MSDN to find out how to query what i need without using [1]/[2]

Answer 1

You're close, just use @* in the nodes function to get all attributes

declare @data xml = '
  <translation id="A">mi casa</translation>
  <translation id="B">
    <div style="width:100px; white-space:normal; line-height:13px;" onclick="DoSomething()">una gato</div>
  </translation>
  <translation id="C">ese</translation>'

select
    t.c.value('local-name(.)', 'nvarchar(max)') as attr,
    t.c.value('../../@id', 'nvarchar(max)') as parent,
    t.c.value('.', 'nvarchar(max)') as value
from @data.nodes('translation/div/@*') as t(c)

sql fiddle demo