none
Regarding usage of XQuery in sql server

    Question

  • i never use xquery and not familiar also. so can anyone tell me when one should use xquery?

    here i am pasting a snippet of xquery where i have bit confusion.

    '<RecordStore>
    
                   <Album id="1" category="Rock">
    
                                 <Artist>Rockers Utd.</Artist>
    
                                 <Title>Rock Until You Drop</Title>
    
                                 <ReleaseYear>2005</ReleaseYear>
    
                                 <Price>10</Price>
    
                   </Album>
    
                   <Album id="2" category="Oldies">
    
                                 <Artist>Oldies Inc.</Artist>
    
                                 <Title>Rock Like There Was A Tomorrow</Title>
    
                                 <ReleaseYear>1960</ReleaseYear>
    
                                 <Price>5</Price>
    
                   </Album>
    
                   <Album id="3" category="Rock">
    
                                 <Artist>ShockRockers</Artist>
    
                                 <Title>ShockRock</Title>
    
                                 <ReleaseYear>2000</ReleaseYear>
    
                                 <Price>12</Price>
    
                   </Album>
    
                   <Album id="4" category="Dance">
    
                                 <Artist>Swing Its</Artist>
    
                                 <Title>Shake and Bake</Title>
    
                                 <ReleaseYear>2005</ReleaseYear>
    
                                 <Price>15</Price>
    
                   </Album>
    
    </RecordStore>'
    
    -- Get the release year for the Album called ShockRock
    
    select @xml.value('(/RecordStore/Album[Title="ShockRock"]/ReleaseYear)[1]', 'int')
    
    -- Get the title for the album with id 4
    
    select @xml.value('(/RecordStore/Album[@id="4"]/Title)[1]', 'varchar(100)')


    just tell me why people use [1] like

    select @xml.value('(/RecordStore/Album[@id="4"]/Title)[1]', 'varchar(100)')

    what is the meaning of [1] ? when one should use 2 instead of 1 like [2] ??

    please explian with sample. thanks

    Sunday, March 03, 2013 6:09 PM

Answers

  • The [1] is known as an ordinal, and just means 'get me the first element', or 'the element at position 1'.  The ordinal can be any number, eg 1, 2, 3.  Calls using .value require an ordinal as you know you only want a scalar value plus it's nice and efficient.  Here's an example:

    DECLARE @xml XML 
    SET @xml = '<root>
      <row>one</row>
      <row>two</row>
      <row>three</row>
    </root>'
    
    SELECT 
    	x.y.value('(row/text())[1]', 'varchar(5)'),
    	x.y.value('(row/text())[2]', 'varchar(5)'),
    	x.y.value('(row/text())[3]', 'varchar(5)')
    FROM @xml.nodes('root') x(y)

    • Edited by wBob Sunday, March 03, 2013 7:03 PM
    • Marked as answer by Mou_kolkata Sunday, March 03, 2013 7:03 PM
    Sunday, March 03, 2013 7:02 PM
  • just tell me why people use [1] like

    select @xml.value('(/RecordStore/Album[@id="4"]/Title)[1]', 'varchar(100)')

    what is the meaning of [1] ? when one should use 2 instead of 1 like [2] ??

    please explian with sample. thanks

    The value method requires that the XQuery expression return no more than one value (a singleton in xml terminology).  This is because a scalar result (single value) is required.  The [1] and [2] refer to the ordinal position of the element within the current context.

    Consider the case of 2 title elements for the selected album.  The singleton reference is needed to avoid ambiguity:

    DECLARE @xml xml = 
    '<RecordStore>
        <Album id="4" category="Dance">
            <Artist>Swing Its</Artist>
            <Title>Shake and Bake</Title>
    	<Title>Shake and Bake2</Title>
            <ReleaseYear>2005</ReleaseYear>
            <Price>15</Price>
        </Album>
    </RecordStore>';
    
    -- Get the first 2 titles for the album with id 4
    SELECT @xml.value('(/RecordStore/Album[@id="4"]/Title)[1]', 'varchar(100)') AS FirstTitle;
    SELECT @xml.value('(/RecordStore/Album[@id="4"]/Title)[2]', 'varchar(100)') AS SecondTitle;
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Mou_kolkata Tuesday, March 05, 2013 7:19 AM
    Sunday, March 03, 2013 7:06 PM
  • [1] means that it is the first element with that tag in that node. This
    example illustrates:

    DECLARE @xml xml =
    '<RecordStore>
                   <Album id="1" category="Rock">
                                 <Artist>Rockers Utd.</Artist>
                                 <Title>Rock Until You Drop</Title>
                                 <Title>Bop til You Drop</Title>
                                 <ReleaseYear>2005</ReleaseYear>
                                 <Price>10</Price>
                   </Album>
                   <Album id="2" category="Oldies">
                                 <Artist>Oldies Inc.</Artist>
                                 <Title>Rock Like There Was A Tomorrow</Title>
                                 <ReleaseYear>1960</ReleaseYear>
                                 <Price>5</Price>
                   </Album>
                   <Album id="3" category="Rock">
                                 <Artist>ShockRockers</Artist>
                                 <Title>ShockRock</Title>
                                 <ReleaseYear>2000</ReleaseYear>
                                 <Price>12</Price>
                   </Album>
                   <Album id="4" category="Dance">
                                 <Artist>Swing Its</Artist>
                                 <Title>Shake and Bake</Title>
                                 <ReleaseYear>2005</ReleaseYear>
                                 <Price>15</Price>
                   </Album>
    </RecordStore>'
    -- Get the release year for the Album called ShockRock
    select @xml.value('(/RecordStore/Album[Title="ShockRock"]/ReleaseYear)[1]',
    'int')
    -- Get the title for the album with id 4
    select @xml.value('(/RecordStore/Album[@id="1"]/Title)[1]', 'varchar(100)')
    select @xml.value('(/RecordStore/Album[@id="1"]/Title)[2]', 'varchar(100)')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Mou_kolkata Tuesday, March 05, 2013 7:20 AM
    Sunday, March 03, 2013 7:28 PM

All replies

  • The [1] is known as an ordinal, and just means 'get me the first element', or 'the element at position 1'.  The ordinal can be any number, eg 1, 2, 3.  Calls using .value require an ordinal as you know you only want a scalar value plus it's nice and efficient.  Here's an example:

    DECLARE @xml XML 
    SET @xml = '<root>
      <row>one</row>
      <row>two</row>
      <row>three</row>
    </root>'
    
    SELECT 
    	x.y.value('(row/text())[1]', 'varchar(5)'),
    	x.y.value('(row/text())[2]', 'varchar(5)'),
    	x.y.value('(row/text())[3]', 'varchar(5)')
    FROM @xml.nodes('root') x(y)

    • Edited by wBob Sunday, March 03, 2013 7:03 PM
    • Marked as answer by Mou_kolkata Sunday, March 03, 2013 7:03 PM
    Sunday, March 03, 2013 7:02 PM
  • just tell me why people use [1] like

    select @xml.value('(/RecordStore/Album[@id="4"]/Title)[1]', 'varchar(100)')

    what is the meaning of [1] ? when one should use 2 instead of 1 like [2] ??

    please explian with sample. thanks

    The value method requires that the XQuery expression return no more than one value (a singleton in xml terminology).  This is because a scalar result (single value) is required.  The [1] and [2] refer to the ordinal position of the element within the current context.

    Consider the case of 2 title elements for the selected album.  The singleton reference is needed to avoid ambiguity:

    DECLARE @xml xml = 
    '<RecordStore>
        <Album id="4" category="Dance">
            <Artist>Swing Its</Artist>
            <Title>Shake and Bake</Title>
    	<Title>Shake and Bake2</Title>
            <ReleaseYear>2005</ReleaseYear>
            <Price>15</Price>
        </Album>
    </RecordStore>';
    
    -- Get the first 2 titles for the album with id 4
    SELECT @xml.value('(/RecordStore/Album[@id="4"]/Title)[1]', 'varchar(100)') AS FirstTitle;
    SELECT @xml.value('(/RecordStore/Album[@id="4"]/Title)[2]', 'varchar(100)') AS SecondTitle;
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Mou_kolkata Tuesday, March 05, 2013 7:19 AM
    Sunday, March 03, 2013 7:06 PM
  • [1] means that it is the first element with that tag in that node. This
    example illustrates:

    DECLARE @xml xml =
    '<RecordStore>
                   <Album id="1" category="Rock">
                                 <Artist>Rockers Utd.</Artist>
                                 <Title>Rock Until You Drop</Title>
                                 <Title>Bop til You Drop</Title>
                                 <ReleaseYear>2005</ReleaseYear>
                                 <Price>10</Price>
                   </Album>
                   <Album id="2" category="Oldies">
                                 <Artist>Oldies Inc.</Artist>
                                 <Title>Rock Like There Was A Tomorrow</Title>
                                 <ReleaseYear>1960</ReleaseYear>
                                 <Price>5</Price>
                   </Album>
                   <Album id="3" category="Rock">
                                 <Artist>ShockRockers</Artist>
                                 <Title>ShockRock</Title>
                                 <ReleaseYear>2000</ReleaseYear>
                                 <Price>12</Price>
                   </Album>
                   <Album id="4" category="Dance">
                                 <Artist>Swing Its</Artist>
                                 <Title>Shake and Bake</Title>
                                 <ReleaseYear>2005</ReleaseYear>
                                 <Price>15</Price>
                   </Album>
    </RecordStore>'
    -- Get the release year for the Album called ShockRock
    select @xml.value('(/RecordStore/Album[Title="ShockRock"]/ReleaseYear)[1]',
    'int')
    -- Get the title for the album with id 4
    select @xml.value('(/RecordStore/Album[@id="1"]/Title)[1]', 'varchar(100)')
    select @xml.value('(/RecordStore/Album[@id="1"]/Title)[2]', 'varchar(100)')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Mou_kolkata Tuesday, March 05, 2013 7:20 AM
    Sunday, March 03, 2013 7:28 PM
  • can i use xquery to insert data from huge xml to our table. if possible can u please give a sample code. thanks
    Tuesday, March 05, 2013 7:19 AM
  • can i use xquery to insert data from huge xml to our table. if possible can u please give a sample code. thanks
    Tuesday, March 05, 2013 7:20 AM
  • can i use xquery to insert data from huge xml to our table. if possible can u please give a sample code. thanks
    Tuesday, March 05, 2013 7:20 AM
  • DECLARE @xml XML
    SET @xml = '<root>
      <row>one</row>
      <row>two</row>
      <row>three</row>
    </root>'

    INSERT tbl(col1, col2, col3)
    SELECT
        x.y.value('(row/text())[1]', 'varchar(5)'),
        x.y.value('(row/text())[2]', 'varchar(5)'),
        x.y.value('(row/text())[3]', 'varchar(5)')
    FROM @xml.nodes('root') x(y)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, March 05, 2013 8:52 AM
  • You can.  There are also a number of other ways to achieve this including OPENXML, SSIS, SQLXML Bulkload API etc.  It really depends on your requirements.  Can you tell us a bit more about what you want to do?
    Tuesday, March 05, 2013 10:39 AM