Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #2058

Re: Unable to query xml data in sql server

Path csiph.com!eternal-september.org!feeder.eternal-september.org!reader01.eternal-september.org!.POSTED!not-for-mail
From Erland Sommarskog <esquel@sommarskog.se>
Newsgroups comp.databases.ms-sqlserver
Subject Re: Unable to query xml data in sql server
Date Tue, 30 Jun 2020 20:39:19 +0200
Organization Erland Sommarskog
Lines 20
Message-ID <XnsABECD21E350A5Yazorman@127.0.0.1> (permalink)
References <b8df0410-a799-45e8-b128-4c8a62b38ed6o@googlegroups.com> <XnsABD8DEF547D6EYazorman@127.0.0.1> <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> <XnsABD9D6547A1DYazorman@127.0.0.1> <e9abcea1-90f7-4055-8a21-1569c4d6e03ao@googlegroups.com> <XnsABDB6BDB76048Yazorman@127.0.0.1> <1bed2a65-5433-4937-8341-754e4bfe4af8o@googlegroups.com> <XnsABE056767CA18Yazorman@127.0.0.1> <15fe54f8-bced-4ff8-a6ba-5ead217270a5o@googlegroups.com> <XnsABE169CB87F1BYazorman@127.0.0.1> <de85ea65-58d9-4359-9be6-0db185fe83c9o@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="231b9b8cb8c2c51e8ffa000d23f6e011"; logging-data="5945"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/u7Bkrm/B/n2KUeJqs9gJP"
User-Agent Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock sha1:G8h66UllcZWUTXhYaFQD4s2iqMQ=
Xref csiph.com comp.databases.ms-sqlserver:2058

Show key headers only | View raw

chris (chris.sjpd@gmail.com) writes:
> DECLARE @x xml = (select xmldata from xmltbl where id betwen 1 and 3)
> SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]', 'varchar(20)') AS [AccidentReportNumber], 
>        Agency.a.value('(NumberInjured-AllPages/text())[1]', 'varchar(20)') AS [NumberInjured],
>        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)
> 

So rather than

> FROM   @x.nodes('/agency') AS Agency(a)


Try 

FROM  xmltbl
CROSS APPLY xmldata.nodes('/agency') AS Agency(a)

And then the second CROSS APPLY after that.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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