I have two result sets from "Query for Multiple Rows as XML". I would like to combined the two based on a common id.
where a.id = b.id add b.* to a
I know that a "SetValue" will allow you to add items to the xml, but how would you do that based on the ids?
Views
Replies
Total Likes
Can you post the two resultsets?
Nith
Views
Replies
Total Likes
Example of the XML:
XML1:
<Books>
<Book id="11">
.......
<AuthorName/>
</Book>
......
</Books>
XML2:
<Authors>
<Author>
<BookId>11</BookId>
<AuthorName>Smith</AuthorName>
</Author>
</Authors>
So in my XPath does this make sense.
Set Value Activity
Location:
../XML1/AuthorName
Expression:
../XML2/Author[BookId == ../XML1/Books/Book/@id]/AuthorName
So, assign the value of Author/AuthorName to Book/AuthorName where Book/@id equals Author/Bookid.
Views
Replies
Total Likes
No! The expression wont produce the expected result.
Because, the expression ../XML2/Author[BookId == ../XML1/Books/Book/@id]/AuthorName will try to look at the 11th child node (author node) of the XML2 node (like an array indexing).
So, (I think) the only way is to iterate each node in XML2 to find and set the value of AuthorName for each Book node in XML1.
Nith
That is not my understanding of XPath.I'm sure that my expression is not correct but if I were to write: ../XML2/Author[BookId = 11] that would return Author where the BookId = 11.
http://msdn.microsoft.com/en-us/library/aa226513%28v=SQL.80%29.aspx
I should be able to take the value(s) from one xml doc and query another. That is the point of XPath.
Views
Replies
Total Likes
I combined the xml documents into one, and used xlt to format the output before he service returned the final xml message. A set value activity was used to do this.
Views
Replies
Total Likes