Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.ms-sqlserver > #2048
| From | Erland Sommarskog <esquel@sommarskog.se> |
|---|---|
| Newsgroups | comp.databases.ms-sqlserver |
| Subject | Re: Unable to query xml data in sql server |
| Date | 2020-06-11 21:04 +0200 |
| Organization | Erland Sommarskog |
| Message-ID | <XnsABD9D6547A1DYazorman@127.0.0.1> (permalink) |
| References | (2 earlier) <25356606-299b-4623-809d-0d6eb9af0906o@googlegroups.com> <XnsABD8F11BF16BFYazorman@127.0.0.1> <4b1c3504-2511-4c3c-a5fb-b880e39caeb6o@googlegroups.com> <XnsABD928EDA5F3Yazorman@127.0.0.1> <07f4ed04-6ddf-42e8-afac-655b93b690e3o@googlegroups.com> |
chris H (chris.sjpd@gmail.com) writes:
> This is all I have to work with :-( The xml file is coming from an
> application which combines some columns from db & others from the xml
> form. I have spent a lot of time to get it work because we only need
> data in a few columns
What you post now is something completely different. That is a complete
XML document, and indeed it is possible to get data out of it. However,
since it is so big, and has so many tags, it takes a while to get all
you need. Here is a start:
SELECT Agency.a.value('(CollisionOccurredDate-AllPages/text())[1]',
'varchar(20)') AS [CollisionOccurredDate-AllPage],
Driver.d.value('(AddressFullText/text())[1]',
'nvarchar(60)') AS AddressFullText
FROM @x.nodes('/agency') AS Agency(a)
CROSS APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver')
AS Driver(d)
You may note that there is no OPENXML. OPENXML was introduced in SQL 2000.
With SQL 2005 we got XQuery, which is generally considered better to
get data out from XML.
The nodees function gives you nodes in the document, and the first is
the root node, and the CollisionOccurredDate is directly below the
root node.
The AddressFullText node on the other hand, is inside the Driver node which
is inside the TrafficUnit node wihch is under the root node. On top of
all there is more than one TrafficUnit node, and it appears you want
the second.
The value function extracts the value a singlee element. The /text() thing
is not needed, but tends to be more efficient.
Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar
Unable to query xml data in sql server chris H <chris.sjpd@gmail.com> - 2020-06-10 09:29 -0700
Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-10 21:55 +0200
Re: Unable to query xml data in sql server chris H <chris.sjpd@gmail.com> - 2020-06-10 13:05 -0700
Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-10 23:42 +0200
Re: Unable to query xml data in sql server chris H <chris.sjpd@gmail.com> - 2020-06-10 14:50 -0700
Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-11 00:15 +0200
Re: Unable to query xml data in sql server chris H <chris.sjpd@gmail.com> - 2020-06-10 15:30 -0700
Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-11 21:04 +0200
Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-06-12 15:54 -0700
Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-13 10:36 +0200
Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-06-17 13:13 -0700
Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-18 08:29 +0200
Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-06-18 16:30 -0700
Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-06-18 16:47 -0700
Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-19 10:24 +0200
Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-06-29 16:56 -0700
Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-06-30 20:39 +0200
Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-07-13 14:25 -0700
Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-07-14 11:49 +0200
Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-07-14 09:56 -0700
Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-07-14 19:59 +0200
Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-07-15 15:20 -0700
Re: Unable to query xml data in sql server chris <chris.sjpd@gmail.com> - 2020-08-04 09:17 -0700
Re: Unable to query xml data in sql server Erland Sommarskog <esquel@sommarskog.se> - 2020-08-04 21:29 +0200
csiph-web