Path: csiph.com!eternal-september.org!feeder.eternal-september.org!reader01.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Unable to query xml data in sql server Date: Thu, 11 Jun 2020 21:04:10 +0200 Organization: Erland Sommarskog Lines: 34 Message-ID: References: <25356606-299b-4623-809d-0d6eb9af0906o@googlegroups.com> <4b1c3504-2511-4c3c-a5fb-b880e39caeb6o@googlegroups.com> <07f4ed04-6ddf-42e8-afac-655b93b690e3o@googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: reader02.eternal-september.org; posting-host="8fb76a8e7458ef31ad142b53d00cba1a"; logging-data="11507"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18gO0z0cHEbUf/t92RwxE8N" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:oPL6o5I35wcu93x2OKSON59UqtE= Xref: csiph.com comp.databases.ms-sqlserver:2048 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.