X-Received: by 2002:a0c:b712:: with SMTP id t18mr3881623qvd.205.1593474998112; Mon, 29 Jun 2020 16:56:38 -0700 (PDT)
X-Received: by 2002:a25:c08a:: with SMTP id c132mr30843345ybf.118.1593474997957; Mon, 29 Jun 2020 16:56:37 -0700 (PDT)
Path: csiph.com!weretis.net!feeder7.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.160.216.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-sqlserver
Date: Mon, 29 Jun 2020 16:56:37 -0700 (PDT)
In-Reply-To: <XnsABE169CB87F1BYazorman@127.0.0.1>
Complaints-To: groups-abuse@google.com
Injection-Info: google-groups.googlegroups.com; posting-host=156.39.0.199; posting-account=l2bzzwoAAABu-fdSSbYhIW6Ic9fVTF16
NNTP-Posting-Host: 156.39.0.199
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>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <de85ea65-58d9-4359-9be6-0db185fe83c9o@googlegroups.com>
Subject: Re: Unable to query xml data in sql server
From: chris <chris.sjpd@gmail.com>
Injection-Date: Mon, 29 Jun 2020 23:56:38 +0000
Content-Type: text/plain; charset="UTF-8"
Xref: csiph.com comp.databases.ms-sqlserver:2057

Hi Erland,

1. All of these xml files are already in a table.

2. The data type of the column in the table is XML.  I try the below query but get the error.


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)

Errors:
Msg 512, Level 16, State 1, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The error makes sense because declaration
DECLARE @x xml = (select xmldata from xmltbl where id betwen 1 and 3)

would return multiple rows & each row is an XML file like this:

xmldata
=========
<agency xmlns........ /agency>
<agency xmlns........ /agency>
<agency xmlns........ /agency>