Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML.Value when it has attributes? Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 7:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1450578
Posted Wednesday, May 8, 2013 8:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:10 AM
Points: 2,587, Visits: 1,626
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


Post #1450603
Posted Wednesday, May 8, 2013 8:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1450607
Posted Wednesday, May 8, 2013 8:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550

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



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1450614
Posted Wednesday, May 8, 2013 8:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:10 AM
Points: 2,587, Visits: 1,626
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

Post #1450625
Posted Wednesday, May 8, 2013 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
Ok the light came on with Marks' wildcard solution, thank you!

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1450630
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse