XML.Value when it has attributes?

Question

Post reply

XML.Value when it has attributes?

Lowell

SSC Guru

Points: 323518
More actions
May 8, 2013 at 7:55 am

#75674

Based on a forum post here, i'm having trouble reading XML when one of the elements has attributes, and was wondering if there is a way to read them when they are mixed?
specifically, the example below has the base tags <Message> with a mix of attributes, which seems ot interfere with me reading tags beneath it. l
<Message version="4.21" xmlns="http://www.surescripts.com/messaging">
...
<Message>
and the simple code i'm using to read one specific value; run the complete code example and you'll see one row returns a null, and the other is populated: I'm thinking there is a technique I've not yet mastered that someone can rub my nose in:
CREATE TABLE [dbo].[surescripts_msg_import] (
[ID] INT IDENTITY(1,1) NOT NULL,
[message] TEXT NULL,
CONSTRAINT [PK__surescri__3214EC27133DC8D4] PRIMARY KEY CLUSTERED ([ID]) )
DELETE FROM surescripts_msg_import
INSERT INTO surescripts_msg_import(message)
--some valid xml as an example= original xml malformed: missing </Pharmacy></RefillRequest> tags
SELECT
'<?xml version="1.0" encoding="utf-8"?>
<Message version="4.21" xmlns="http://www.surescripts.com/messaging">
<Header></Header>
<Body>
<RefillRequest>
<RxReferenceNumber>3349|1584461|1|0|1</RxReferenceNumber>
<PrescriberOrderNumber>489a819117344e61a194c28e5f9128d8</PrescriberOrderNumber>
<Pharmacy>
<Identification><NCPDPID>2356295</NCPDPID></Identification>
<StoreName>WALGREENS 3349</StoreName>
<Pharmacist><LastName>WLR</LastName></Pharmacist>
</Pharmacy>
</RefillRequest>
</Body>
</Message>'
INSERT INTO surescripts_msg_import(message)
--some valid xml as an example= original xml malformed: missing </Pharmacy></RefillRequest> tags
SELECT
'<?xml version="1.0" encoding="utf-8"?>
<Message>
<Header></Header>
<Body>
<RefillRequest>
<RxReferenceNumber>3349|1584461|1|0|1</RxReferenceNumber>
<PrescriberOrderNumber>489a819117344e61a194c28e5f9128d8</PrescriberOrderNumber>
<Pharmacy>
<Identification><NCPDPID>2356295</NCPDPID></Identification>
<StoreName>WALGREENS 3349</StoreName>
<Pharmacist><LastName>WLR</LastName></Pharmacist>
</Pharmacy>
</RefillRequest>
</Body>
</Message>'
--test if valid xml or not?
SELECT ID,CONVERT(xml,message) FROM [surescripts_msg_import]
--now start shredding the xml
WITH XMLNAMESPACES ('http://www.surescripts.com/messaging' AS sp)
SELECT *,
RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/Message/Body/RefillRequest/RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')
FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]
)MyAlias
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Arthur Olcot SSCertifiable Points: 6652 More actions · Answer 1

I think you are just missing the namespace reference as part of the XPath... Try this:

--now start shredding the xml

WITH XMLNAMESPACES ('http://www.surescripts.com/messaging' AS sp)

SELECT *,

RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/sp:Message/sp:Body/sp:RefillRequest/sp:RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')

FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]

)MyAlias

Lowell SSC Guru Points: 323518 More actions · Answer 2

thanks, arthur, but that's kind of what's confusing me;

if i don't use the namespace, i get the desired results for the second item but not ht efirst; if i use the name space, i get the first item, but not the second

is it because, once you use a namespace, if it's not mentioned it's malformed?

--now start shredding the xml

WITH XMLNAMESPACES ('http://www.surescripts.com/messaging' AS sp)

SELECT *,

RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/sp:Message/sp:Body/sp:RefillRequest/sp:RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')

FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]

)MyAlias

--now start shredding the xml

WITH XMLNAMESPACES ('http://www.surescripts.com/messaging' AS sp)

SELECT *,

RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/Message/Body/RefillRequest/RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')

FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]

)MyAlias

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 3

Quick 'n dirty solution

SELECT *,

RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/*:Message/*:Body/*:RefillRequest/*:RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')

FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]

)MyAlias

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Arthur Olcot SSCertifiable Points: 6652 More actions · Answer 4

Mark-101232 (5/8/2013)
Quick 'n dirty solution
SELECT *,
RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/*:Message/*:Body/*:RefillRequest/*:RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')
FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]
)MyAlias

Nice one there Mark.. I just came up with this, but i prefer yours for readability.. I'll have to remember your one. 🙂

--now start shredding the xml

WITH XMLNAMESPACES ('http://www.surescripts.com/messaging' AS sp)

SELECT *,

RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/Message/Body/RefillRequest/RxReferenceNumber/text(),

(/sp:Message/sp:Body/sp:RefillRequest/sp:RxReferenceNumber/text())) [1]', 'NVARCHAR(256)')

FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]

)MyAlias

Lowell SSC Guru Points: 323518 More actions · Answer 5

Ok the light came on with Marks' wildcard solution, thank you!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!