SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML.Value when it has attributes?


XML.Value when it has attributes?

Author
Message
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27938 Visits: 39921
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!

Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 1779
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
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27938 Visits: 39921
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
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3083 Visits: 24042
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
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 1779
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
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27938 Visits: 39921
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search