Forum Replies Created

Viewing 15 posts - 1 through 15 (of 41 total)

  • RE: Odd results reading attributes when schema applied

    Select X.N.value('string(@id)', 'int') As v1 From @x1.nodes('root') X(N)

    Here is a link to a similar case with an explanation on why by the designer of the XML...

  • RE: Cross apply / XML shredding -- mutiple nodes creating problems

    select O.X.value('@orderNumber', 'varchar(20)') as OrderNumber,
           I.X.value('@itemNumber', 'varchar(10)') as ItemNumber,
           I.X.value('@qty', 'varchar(10)') as Qty,
           I.X.value('@rate', 'float') as Rate
    from @x.nodes('/root/salesOrder')...

  • RE: XQuery error

    Using the parent axis when extracting values from XML is not good for performance. 
    Use an extra cross apply instead.

    select P.X.value('(PackID/text())[1]','int') as PackID,

  • RE: Help with XML Splitter De-entitization, please.

    Hi Jeff!

    Sorry for the late reply. I guess holidays came along and this thread fell out of focus.

    Your observation are correct about the behaviour of splitting on text(). It will...

  • RE: Help with XML Splitter De-entitization, please.

    To fix the problem with XML special characters in your splitter you can use "select @List for xml path('')" to create entities for your invalid characters.

    The entities will be...

  • RE: Thanksgiving

    Which of these queries will work consistently on every Thanksgiving?

    Actually none since limitations of the data type but number 2 works for more years then number 1 so well ......

  • RE: Union Date/Time Data Types

    If you let this run for a while you will eventually get 1 row back from the query.

    declare @T table(Value datetime2);

    declare @I int = 0

    while 1 = 1



  • RE: Modifying XML with into or after

    declare @X xml = '

    <row id="1000000" xml:space="preserve">


    <c2>Text Sample 1</c2>

    <c2 m="2">Text Sample 2</c2>

    <c2 m="3">Text Sample 3</c2>

    <c2 m="4">Text Sample 4</c2>

    <c3>Text Sample 1</c3>

    <c3 m="2">Text Sample 2</c3>

    <c3 m="3">Text Sample 3</c3>

    <c4>Text Sample</c4>

    <c5>Text Sample</c5>


    set @X.modify('insert <ctest>New...

  • RE: Unable to link data from data pulled from XML

    Alan.B (8/17/2015)

    Performance aside, including the text() node is just good programming. Without it you are relying on the parser's default behavior to get you your result. Without it, what...

  • RE: Cannot use Group By when CASTing XML data

    Put the query you have, without the group by, in a derived table and do the group by in the main query.

    Not sure about your table structures and relations but...

  • RE: Extract the titles from XML

    Doing it with a ten million leaf nodes instead of just four might deliver quite different results. :hehe:

    FYI: the plan will be identical regardless of what your XML variable contains....

  • RE: Extract the titles from XML

    Eirikur Eiriksson (7/13/2015)



    FROM @TXML.nodes('//name') AS NAME(DATA)






    //name and /STUFF_FROM_MSDB/SYSTABS/name/text() is not equivalent. The first...

  • RE: Extract the titles from XML

    I guess this is the preferred form.


    col.value('(Title/text())[1]', 'varchar(50)') AS 'Book'

    FROM @x.nodes('/root/Books/Book') a(col)

    Using text() in the values clause removes one call to a table valued function...

  • RE: XQUERY syntax question; how do i extract the GUID?

    The nodes specs and name have a namespace. You can use * as wildcard character for a namespace or you can use WITH XMLNAMESPACES (Transact-SQL).

    .value('(/includeSelectionItemsTemp/ArrayOfObjectSelector/ObjectSelector/*:specs/*:name/text())[1]', 'uniqueidentifier')

Viewing 15 posts - 1 through 15 (of 41 total)