XML-Choosing Child Nodes-SQL



I am trying to write the correct SQL code, for a XML to SQL import that will allow me to choose between the below UnitNumber 1, 2, or 3. Please review. Right now When I use x.query I am returning all values for PersonName, which would be all PersonName's for UnitNumber 1,2,or 3 I need to just return the PersonName for Unit 1 then 2 and so on.



<Units xmlns="http://ift.tt/15aDqLg">
<d3p1:VehichleUnit xmlns:d3p1="http://ift.tt/15aDqLg"d3p1:UnitNumber="1">
<PersonName>
<PersonGivenName xmlns="http://ift.tt/1nOCPaB">Jim </PersonGivenName>
<PersonLastName xmlns="http://ift.tt/1nOCPaB">Brown
</PersonName>
<d3p1:VehichleUnit xmlns:d3p1="http://ift.tt/15aDqLg"d3p1:UnitNumber="2">
<d3p1:VehichleUnit xmlns:d3p1="http://ift.tt/15aDqLg"d3p1:UnitNumber="3">
</Units>

WITH xmlnamespaces (DEFAULT 'http://ift.tt/1tNQ09G',
'http://ift.tt/ra1lAU' as
dop,'http://ift.tt/15aDqLk' as dop1,'http://ift.tt/1nOCPaB' as dop2,
'http://ift.tt/15aDqLg' as d3p1,'http://ift.tt/1ysjSxT' as dop3)
INSERT INTO ACRSVehicle (LocalCaseNumber,GoingDirection ,ContinueDirection,CitationIssued,AtFault,Citati onCode,DriverFirstName)
SELECT X.p.query ('/ACRSReport/d3p1:Units//dop3:Driver/dop3:PersonName/dop2:PersonGivenName').val ue('.', 'VARCHAR(25)')
FROM ( SELECT CAST(x AS XML)
FROM OPENROWSET(BULK 'D:ACRS\MONTGOMERY_acrs_MCP0036000Q.xml',SINGLE_BLOB)
AS T(x))
AS T(x)CROSS APPLY x.nodes('/ACRSReport') AS X(p) ;

No comments:

Post a Comment