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

Re: Unable to query xml data in sql server

From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Unable to query xml data in sql server
Date	2020-06-11 00:15 +0200
Organization	Erland Sommarskog
Message-ID	<XnsABD928EDA5F3Yazorman@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>

Show all headers | View raw

chris H (chris.sjpd@gmail.com) writes:
> That is a complete xml file.  When I doubleclick to open it, it does not have root tag. 
> 

I would say it's not an XML file then. An XML file has exactly a single root tag. SQL Server supports repeated root tags, but there are no root tags at all here.

I get this error from sp_xml_preparedocument:

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1 [Batch Start Line 0]
The error description is 'Only one top level element is allowed in an XML document.'.


DECLARE @x xml = 
' <AccidentReportNumber-AllPages>2020-200320523</AccidentReportNumber-AllPages>
 <AddressFullText>1493 CRESTHAVEN LN,SAN JOSE, CA,  95118</AddressFullText>
 <InjuredWas_driver>1</InjuredWas_driver>
 <PedestrianActionA-AllPages>1</PedestrianActionA-AllPages>
 <MotorVehicleInvolvedWithC-AllPages>1</MotorVehicleInvolvedWithC-AllPages>
 <CollisionOccurredDate-AllPages>02-01-2020</CollisionOccurredDate-AllPages>
 <Time-AllPages>1547</Time-AllPages>'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @x


 SELECT *
FROM OPENXML(@hDoc, '/', 1)
WITH 
(
	[AccidentReportNumber-AllPages] varchar(20),
    AddressFullText varchar(100) '/trafficunit/driver',
    "InjuredWas_driver" int '/involvedPeople',
    [PedestrianActionA-AllPages] int,
    [MotorVehicleInvolvedWithC-AllPages] int,
    [CollisionOccurredDate-AllPages]  date,
    [Time-AllPages]  int
)

EXEC sp_xml_removedocument @hDoc OUTPUT

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