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

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

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, 14 Jul 2020 19:59:09 +0200
Organization Erland Sommarskog
Lines 19
Message-ID <XnsABFACB4EBCD2CYazorman@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> <XnsABECD21E350A5Yazorman@127.0.0.1> <4a865c7f-1a4f-4398-9150-36195d16012co@googlegroups.com> <XnsABFA78473A378Yazorman@127.0.0.1> <bc09787e-6d86-4b0a-8df7-965c6432e956o@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="b610f31ce8b263fa2f931e56c3e8a147"; logging-data="24060"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+5ClXgxJcBns2RNjKNyvl1"
User-Agent Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock sha1:QmE7oXSp/hzs5PUOYUj7CYxw7Nk=
Xref csiph.com comp.databases.ms-sqlserver:2063

Show key headers only | View raw

> I need to query out the XML data in a table 's column with XML datatype.
> The "select xmldata from xmltbl where ..." statement will return several
> rows.  Currently I can get the xmldata if the SELECT statement returns a
> single row, but the SELECT statement always returns multiple rows.  My
> goal is to get the result set of XML data in all of these rows at one. 
> Please let me know how I can achieve it. 
>

What happens if you run

 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  xmltbl
 CROSS APPLY xmldata.nodes('/agency') AS Agency(a)
 CROSS  APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS Driver(d)


That is, the same thing except the DECLARE statement which you don't need?

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