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: Thu, 11 Jun 2020 00:15:05 +0200
Organization: Erland Sommarskog
Lines: 39
Message-ID: <XnsABD928EDA5F3Yazorman@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>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: reader02.eternal-september.org; posting-host="8fb76a8e7458ef31ad142b53d00cba1a"; logging-data="314"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1+jWuHFGxB68h8FyLznrKnk"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:xyTNYNucoR081IcAK0tiIUAmQZQ=
Xref: csiph.com comp.databases.ms-sqlserver:2044

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