-
May 24th, 2023, 09:41 AM
#1
[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
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
May 25th, 2023, 07:06 AM
#2
Re: Select and Get rows according to XML mssql 2008
Originally Posted by sapator
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
-
May 25th, 2023, 08:01 AM
#3
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|