July 24, 2007 at 11:37 am
Ok, I've been racking my brains on this for hours. How can I get the "name" of the Node that I am currently iterating over in an XQuery "query".
Example:
SELECT @data.query('
for $p in /Table/Row/Columns/*
return
<Column Name="{$p???}">{$p/text()}</Column>
') as Result
The above example is trying to take the output from an "FOR XML AUTO, ELEMENTS" query and take each Column Element that is returned and noramlize the xml. For some reason, no matter what I try, I can't get the "$p" variable to give me it's name. I know that it is on the correct element.
So instead of:
<Table>
<ID>1</ID>
<Name>Name 1</Name>
</Table>
it should be:
<Table>
<Column Name="ID">1</Column>
<Column Name="Name">Name 1</Column>
</Table>
July 24, 2007 at 1:16 pm
Ok, I solved my own problem. Since you will not find a solution anywhere on the internet, here it is. It only took 9 hours of searching the internet to finally fall upon a Microsoft link (http://msdn2.microsoft.com/en-us/library/ms190650.aspx) that lead to the solution.
Here's what the query should look like. This basically transforms all that crappy FOR XML AUTO into a nice reusable Xml structure for any table. I use this for Audit Tracking by transforming all tables into the same structure of Xml for easy parsing out later on.
Declare @data Xml, @Data1 Xml, @Data2 Xml
Select @Data1 = (Select Top 1 * From Products For Xml Auto, Elements)
Select @Data2 = (Select Top 1 * From Products Order By ID DESC For Xml Auto, Elements)
Set @data = Convert(Xml, '<Table>'
+ '<Row Version="Original">' + Convert(VarChar(MAX), @Data1) + '</Row>'
+ '<Row Version="Current">' + Convert(VarChar(MAX), @Data2) + '</Row>'
+ '</Table>')
Select @data
SELECT @data.query('
for $p in /Table/Row[@Version="Original"]/Products/*
return
<Column Name="{local-name($p)}">{$p/text()}</Column>
') as Result
It's not fully complete yet, but eventually I'll have something similar to the following for Auditing table updates.
<Table>
<Row Version="Original">
<Column Name="ID"><![CDATA[1]]></Column>
<Column Name="Name"><![CDATA[Test]]></Column>
</Row>
<Row Version="Current">
<Column Name="ID"><![CDATA[1]]></Column>
<Column Name="Name"><![CDATA[Test]]></Column>
</Row>
</Table>
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply