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>
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

> 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?