XML, SSIS, and counting Tags

Question

Post reply

XML, SSIS, and counting Tags

Evil Kraig F

SSC Guru

Points: 100851
More actions
May 24, 2013 at 4:27 pm

#273119

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.
- Craig Farrell
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
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

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);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

...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);

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

For the love of gods...

THANK YOU.

I owe you a beer. Or ten.

- Craig Farrell

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

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);

XML, SSIS, and counting Tags

Cookies on SQLServerCentral