x

XML Query

My XML is below. I'm having a hard time trying to query the information from this. Just trying to get the first_name, last_name, ssn for starters. If I can get on the right path with that, I think I can figure out the rest.

XML is attached.link text

test.xml (5.2 kB)
more ▼

asked Feb 23, 2015 at 02:11 AM in Default

avatar image

smithstj
10 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Because in you XML is defined a namespace, you have to use the namespace also in the query. And because the namespace is not prefixed, you need to define a namespace prefix and then use that prefix everywhere.

You can achieve this using the WITH XMLNAMESPACES or define the namespace directly in the query. Below are some xamples:

Using the WITH XMLNAMESPACES

Please note below that even the xmlns="http://www.website.com/CLF" has no prefix we have to define one and use it in the uqery. Also we have to prefix all the elements in the path.

Get the first Applicant information:

 DECLARE @xml xml = N'<<YOUR XML HERE>>'
 ;WITH XMLNAMESPACES('http://www.website.com/CLF' as v)
 SELECT    
     @xml.value('(v:VEHICLE_LOAN/v:APPLICANTS/v:APPLICANT/@first_name)[1]', 'nvarchar(50)') AS FirstName
     ,@xml.value('(v:VEHICLE_LOAN/v:APPLICANTS/v:APPLICANT/@last_name)[1]', 'nvarchar(50)') AS LastName
     ,@xml.value('(v:VEHICLE_LOAN/v:APPLICANTS/v:APPLICANT/@ssn)[1]', 'nvarchar(10)') AS SSN


Get information for all applicants in any vehicle loan:

 DECLARE @xml xml = N'<<YOUR XML HERE>>'
 ;WITH XMLNAMESPACES('http://www.website.com/CLF' as v)
 SELECT    
     n.value('./@first_name', 'nvarchar(50)') AS FirstName
     ,n.value('./@last_name', 'nvarchar(50)') AS LastName
     ,n.value('./@ssn', 'nvarchar(10)') AS SSN
 FROM @xml.nodes('v:VEHICLE_LOAN/v:APPLICANTS/v:APPLICANT') T(n)

Define the namespace inside the query

Again, note that we have to define the namespace prefix and use it everywhere.

Get the first Applicant information:

 DECLARE @xml xml = N'<<YOUR XML HERE>>'
 SELECT    
     @xml.value('declare namespace v="http://www.website.com/CLF"; (v:VEHICLE_LOAN/v:APPLICANTS/v:APPLICANT/@first_name)[1]', 'nvarchar(50)') AS FirstName
     ,@xml.value('declare namespace v="http://www.website.com/CLF"; (v:VEHICLE_LOAN/v:APPLICANTS/v:APPLICANT/@last_name)[1]', 'nvarchar(50)') AS LastName
     ,@xml.value('declare namespace v="http://www.website.com/CLF"; (v:VEHICLE_LOAN/v:APPLICANTS/v:APPLICANT/@ssn)[1]', 'nvarchar(10)') AS SSN

Get information for all applicants in any vehicle loan:

 DECLARE @xml xml = N'<<YOUR XML HERE>>'
 SELECT    
     n.value('./@first_name', 'nvarchar(50)') AS FirstName
     ,n.value('./@last_name', 'nvarchar(50)') AS LastName
     ,n.value('./@ssn', 'nvarchar(10)') AS SSN
 FROM @xml.nodes('declare namespace v="http://www.website.com/CLF"; v:VEHICLE_LOAN/v:APPLICANTS/v:APPLICANT') T(n)


Based on the provided XML all the above samples will produce below result:

 FirstName   LastName      SSN
 ----------- ------------- ----------
 DENZEL      WASHINGTON    666045587


If you would like to use also the xmlns:d3p1="http://www.website.com/InternalUse" namespace, you have to define it also and use tha prefix.

For more details see xml Data Type Methods on MSDN.

more ▼

answered Feb 23, 2015 at 10:18 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Wow. Thanks for the help Pavel

Feb 23, 2015 at 03:25 PM smithstj

You can actually use the "default" keyword in your namespace declaration and leave out all the "v:" prefixes. WITH XMLNAMESPACES(default 'http://www.website.com/CLF') SELECT
@xml.value('(VEHICLE_LOAN/APPLICANTS/APPLICANT/@first_name)[1]', 'nvarchar(50)') AS FirstName ,@xml.value('(VEHICLE_LOAN/APPLICANTS/APPLICANT/@last_name)[1]', 'nvarchar(50)') AS LastName ,@xml.value('(VEHICLE_LOAN/APPLICANTS/APPLICANT/@ssn)[1]', 'nvarchar(10)') AS SSN;

Feb 23, 2015 at 11:46 PM Mister Magoo

Good point about the DEFAULT :-)

Feb 24, 2015 at 06:07 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x204

asked: Feb 23, 2015 at 02:11 AM

Seen: 70 times

Last Updated: Feb 24, 2015 at 06:07 AM

Copyright 2018 Redgate Software. Privacy Policy