Results 1 to 3 of 3

Thread: [RESOLVED] Select and Get rows according to XML mssql 2008

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Resolved [RESOLVED] Select and Get rows according to XML mssql 2008

    Hi.
    I'm reading all kind of document .
    We have 2008R2 server and I want to get values from nvarchar and also find values from nvarchar
    let's say the table name is person , how do I get the values in fields and select the criterial from a nvarchar(but xml) field?

    Let's say I have this in a field:
    <person><IsInvoiced>1</IsInvoiced><PaidDate>20230523</PaidDate><town>Athens</town></person>

    How do I select it?
    Code:
    select *
     from person
     OPENXML (person_xmlfield, '/ROOT/',1)
                WITH (IsInvoiced  varchar(10),
                      PaidDate datetime)
     where person_id = '2U61YLKV91'
     and  OPENXML (person_xmlfield, '/ROOT/',1)
                WITH (IsInvoiced  varchar(10),
                      PaidDate datetime) == ??no clue
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Select and Get rows according to XML mssql 2008

    Quote Originally Posted by sapator View Post
    Hi.
    We have 2008R2 server ...

    ...how do I get the values in fields and select the criterial from a nvarchar(but xml) field?
    Not sure, how far back the support for the xml-datatype is reaching -
    which I'm casting to (from an nvarchar-field) in the fiddle below:
    http://sqlfiddle.com/#!18/fe7fc/4

    The fiddle offers only a MS-SQLServer 2017 instance, to test things...

    HTH

    Olaf

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Select and Get rows according to XML mssql 2008

    Thanks mate. It works.
    Funny thing, they decided to go with Json and currently use "like" on the string until we update to a newer server version.
    Typical, putting in work hours for Nada.
    Last edited by sapator; May 25th, 2023 at 08:20 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width