1

Source: https://stackoverflow.com/a/56825537/3037607

Select A.[ID]
      ,C.*
 From  YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData) -- This B here
 Cross Apply (
                Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Value = xAttr.value('.','varchar(max)')
                 From  XMLData.nodes('//@*') xNode(xAttr)
                 Where xAttr.value('local-name(.)','varchar(100)') not in ('Id','Other-Columns','To-Exclude')
             ) C
 Join Source D on A.ID=D.ID and C.Item=D.TargetField

I tried this on my own database, but I can't wrap my head around what the B means in B(XMLData). When I remove the B, I get the following error:

No column name was specified for column 1 of 'XMLData'.

But when I replace it by (for example) helloworld(XMLData), the query still works...

I haven't been able to find a answer online which explains this for me.

Mason
  • 1,007
  • 1
  • 13
  • 31
  • 2
    basically it is a table alias, as you don't use it in the select, you can name it as you want, nothing would change. But when you add b.* to your select you will see what it contains – nbk Mar 06 '23 at 10:55

0 Answers0