none
Search for XML string

    Question

  • Hey I am searching for a string search function of a XML field.

    The table is named historie and the field is named DatenNeu (XML(.), nul)

    Example data on field DatenNeu:

    <dataset>
      <field name="ID">237782</field>
      <field name="PersonenID">43510</field>
      <field name="FahrzeugID" />
      <field name="AusweisID" />
      <field name="ZutrittProfilID">128</field>
      <field name="ZeitProfilID">0</field>
      <field name="Status">1</field>
      <field name="GueltigVon">20130731 00:00:00</field>
      <field name="GueltigBis">20140630 00:00:00</field>
      <field name="DynData" />
    </dataset>

    Now i would like to find the TOP 100 entries with "ZutrittProfilID" = '128'

      SELECT TOP 100 *
      from Historie
      WHERE CAST(ZutrittProfilID AS nvarchar(max)) LIKE N'%128%'
      order by ID desc

    MSSQL studio2008 R2

    is telling me: "Invalid column name 'ZutrittProfilID'.

    Anybody able to help? :)

    Thanks in advance.

    Olli



    Wednesday, July 31, 2013 8:31 AM

Answers

  • Hi,

    First of all this is not the appropriate forumn for your question - post in SQL Server XML forumn -

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlxml

    Try like this ,

    DECLARE @X XML = '<dataset>
       <field name="ID">237782</field>
       <field name="PersonenID">43510</field>
       <field name="FahrzeugID" />
       <field name="AusweisID" />
       <field name="ZutrittProfilID">128</field>
       <field name="ZeitProfilID">0</field>
       <field name="Status">1</field>
       <field name="GueltigVon">20130731 00:00:00</field>
       <field name="GueltigBis">20140630 00:00:00</field>
       <field name="DynData" />
       
       <field name="ID">237783</field>
       <field name="PersonenID">43510</field>
       <field name="FahrzeugID" />
       <field name="AusweisID" />
       <field name="ZutrittProfilID">129</field>
       <field name="ZeitProfilID">0</field>
       <field name="Status">1</field>
       <field name="GueltigVon">20130731 00:00:00</field>
       <field name="GueltigBis">20140630 00:00:00</field>
       <field name="DynData" />
     </dataset> '
      
    SELECT TOP 2 i.value('.', 'varchar(50)') AS ZutrittProfilID 
    FROM @X.nodes('/dataset/field') AS X(i)
    WHERE i.value('@name', 'varchar(50)') = 'ZutrittProfilID'
    AND i.value('.', 'varchar(50)') = '128'
    
    SELECT TOP 2 i.value('.', 'varchar(50)') AS ZutrittProfilID
    FROM @X.nodes ('/dataset/field[@name="ZutrittProfilID"][.="128"]') X(i) 
    
    DECLARE @historie  TABLE (Id INT,DatenNeu XML)
    INSERT @historie SELECT 1,'<dataset>
       <field name="ID">237782</field>
       <field name="PersonenID">43510</field>
       <field name="FahrzeugID" />
       <field name="AusweisID" />
       <field name="ZutrittProfilID">128</field>
       <field name="ZeitProfilID">0</field>
       <field name="Status">1</field>
       <field name="GueltigVon">20130731 00:00:00</field>
       <field name="GueltigBis">20140630 00:00:00</field>
       <field name="DynData" />
        </dataset>'
    INSERT @historie SELECT 2,'<dataset> 
       <field name="ID">237783</field>
       <field name="PersonenID">43510</field>
       <field name="FahrzeugID" />
       <field name="AusweisID" />
       <field name="ZutrittProfilID">128</field>
       <field name="ZeitProfilID">0</field>
       <field name="Status">1</field>
       <field name="GueltigVon">20130731 00:00:00</field>
       <field name="GueltigBis">20140630 00:00:00</field>
       <field name="DynData" />
     </dataset> '
    SELECT TOP 100 *
    FROM @historie
    WHERE DatenNeu.exist('dataset/field[@name="ZutrittProfilID"][. = "128"]') = 1
    
    


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    • Marked as answer by Olli Grewenig Thursday, August 1, 2013 8:30 AM
    Wednesday, July 31, 2013 9:23 AM