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

Re: Unable to query xml data in sql server

Newsgroups	comp.databases.ms-sqlserver
Date	2020-06-17 13:13 -0700
References	(5 earlier) <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>
Message-ID	<1bed2a65-5433-4937-8341-754e4bfe4af8o@googlegroups.com> (permalink)
Subject	Re: Unable to query xml data in sql server
From	chris <chris.sjpd@gmail.com>

Show all headers | View raw

Hi Erland,

Could you please be more specific on how to read xml data in multiple xml files given all of the xml files have the same structure?

My below query only works for a single xml file, but I need to query the data from a bunch of files.


declare @x xml = (select xmldata from xmltbl)
SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]', 'varchar(20)') AS [AccidentReportNumber], 
       Agency.a.value('(CollisionOccurredDate-AllPages/text())[1]', 'varchar(20)') AS [CollisionOccurredDate],
       Agency.a.value('(Time-AllPages/text())[1]', 'varchar(20)') AS [Time],
          Agency.a.value('(MotorVehicleInvolvedWithI-AllPages/text())[1]', 'varchar(20)') AS [MotorVehicleInvolvedWithI],
          Agency.a.value('(PedestrianActionA-AllPages/text())[1]', 'varchar(20)') AS [PedestrianAction],
          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)

I really appreciate your helps/inputs.

Thank you,
Chris

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