I'm trying to plug a new vendor into an existing workflow process. The old workflow weaves between external programs and Sql Server, and the output of one of the phases is an xml document. I'm trying to mimic the xml document, only using FOR XML PATH.
It's a pretty basic <Items><Item ID="1" /><Item ID="2" /><Item ID="3" /></Items> kind of document. The complicating things are
- The sequential ID attribute on the top of each <Item> and
- The heterogeneous nature of what's in each <Item>. You can have <Item ID="1"><Color>Red</Color></Item> followed by <Item ID="2"><Size>Big</Size></Item>. The contents of each element can come from different tables and have different attributes.
The Items are normalized many-to-one objects around a main table. Each normalized related table has different attributes (as mentioned above, one may have size another object may have color). The xml document is a denormalization around the main object.
The sequential id spans all the normalized types.
As I said, the old process for old vendors is weaving back and forth between Sql Server and external processes; the new vendor already has the data in normalized tables, but I'm trying to plug in this xml document directly from the tables with a sql query, but to recreate the xml denormalized representation, I'm trying to get this sequential id that spans the many-to-one sub tables.
I'd hoped that I could get the sequential ID with ROW_NUMBER() over a UNION ALL but found FOR XML PATH doesn't work with UNION ALL - it throws an error.
I tried a few different ways of using XQUERY modify() but couldn't find a way that generated sequential ids and let me modify multiple nodes at once.
Does anyone have any ideas?
Thanks.
