XML, SSIS, and counting Tags

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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

  • mister.magoo

    SSC-Forever

    Points: 47068

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

  • 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: 47068

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

  • 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]

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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: 47068

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

  • 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]

    Viewing 5 posts - 1 through 5 (of 5 total)

    You must be logged in to reply to this topic. Login to reply