May 24, 2013 at 4:27 pm
Right, so. I've got a problem. I need to have a 'tag count' included in the footer of an XML I'm shipping to a system. The problem comes in where there's sublevels in tags that throw off @@ROWCOUNT and running it twice (once for XML and once for count) could give me different results, the system has enough volume and the XML builder travels for 4+ seconds.
So, even creating an @@ROWCOUNT solution, SSIS as an OLEDB source doesn't let you do output parameters. You can ship it to an Execute SQL command at the Control Flow level, stuff the recordset into an object variable, and then grab that in a script source in the dataflow, but that is not only overkill, it's moronic... but if it's the only way it's the only way. The problem is that any rowcounts I get will include the sublevels.
Errr, I suppose I should explain that.
<Contact name="a">
<Address Type="Home">
<Address Type="Business">
</Contact>
<Contact name="b" />
<Contact name="c">
<Address Type="Home">
</Contact>
I need to get a count of <Contact without including the count of <Address, but if you @@ROWCOUNT after your FOR XML you get the full count. I need 3, the above would give me 6.
Now, usually what I'd do is simply multi-cast in SSIS at this point and strip the little bugger in a script task doing locational checks. Problem... VARCHAR(MAX) (or DT_Text) doesn't allow itself to be shoved into a string type in VB.NET... which I need because these things can get huge.
Great.
At this point I'm interested in knowing if anyone has even an idea of alternat ways to go. I'm tacking the footer on in another dataflow which is dependent on the XML inclusion, so Pre-processing of some form is an acceptable idea. I may just not know the right VB.Net variable type to use, so if that's the problem please, send me in the right direction. If it's obvious, my google-fu has failed due to keywords. For example, I can easily find things like this: http://blog.boxedbits.com/archives/6, but I can't find a workaround.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 24, 2013 at 4:32 pm
I'm missing the point I am sure, so can you explain why you can't just count the nodes with a select?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 24, 2013 at 4:45 pm
...I'm thinking something like this:
declare @x xml = (select *
from sys.tables as tables
for xml auto,elements)
select @x,(select count(*) from @x.nodes('tables') [count](nd) for xml auto,elements,type)
from (values(1)) as [root](b)
for xml auto,elements
Which produces
<root>
...
<tables>
<name>XMLTest</name>
<object_id>2105058535</object_id>
...
<lock_escalation_desc>TABLE</lock_escalation_desc>
<is_filetable>0</is_filetable>
</tables>
[highlight="#ffff11"]<count>41</count>[/highlight]
</root>
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 24, 2013 at 4:56 pm
For the love of gods...
THANK YOU. 😀
I owe you a beer. Or ten.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 24, 2013 at 5:01 pm
Evil Kraig F (5/24/2013)
For the love of gods...THANK YOU. 😀
I owe you a beer. Or ten.
I'll take the THANK YOU as enough... Beer is for lizards...:hehe:
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply