May 7, 2013 at 10:18 am
Folks
I have a table with a column "message" whose type is "TEXT".
The contents inside this column is XML.
Here is a sample:
<?xml version="1.0" encoding="utf-8"?><Message version="4.21" xmlns="http://www.surescripts.com/messaging"><Header>....
<Address><AddressLine1>1991 E APPLE AVE</AddressLine1><City>MUSKEGON</City>
How do a write a query to retrieve rows that have a CITY= 'Muskegon'
Select top 100 * from surescripts_msg_import
where
???? ( you will the rest ).
Thanks in advance
May 7, 2013 at 10:42 am
your xml was not well formed, so i couldn't use it as an example to show you:
you will have to convert(xml,YourTEXTColumn) to use this logic;
here's a couple of ways:
DECLARE @xml XML
SET @xml=
'<root>
<id>2983</id>
<Area>Urgencias</Area>
<Puesto>Enfermera General</Puesto>
<Motivo>Reposicion de personal</Motivo>
<Genero>F</Genero>
</root>'
SELECT
T.c.value('localname[1]', 'varchar(100)') AS element, -- node name extracted from data column (holding NTFRS elements in xml format)
T.c.value('value[1]', 'varchar(100)') AS val -- node value extracted from data column
FROM
(SELECT
S.c.query('
for $node in /descendant::node()[local-name() != ""]
return <node>
<localname>{ local-name($node) }</localname>
<value>{ $node }</value>
</node>') AS nodes
FROM @xml.nodes('.') S(c)
) subqry --returns content of intermdiate table with each xml node and value separated, but still in xml format
CROSS APPLY subqry.nodes.nodes('/node') AS T(c) -- anchor for extracting the values of the xml data
WHERE T.c.value('localname[1]', 'varchar(100)') <>'root'
and another:
DECLARE @XML XML;
SELECT @XML =
'<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>';
SELECT --R.Node.query('.'),
R.Node.query('.').value('(/Customers/CustomerId/.)[1]','varchar(100)') AS CustomerID,
R.Node.query('.').value('(/Customers/CompanyName/.)[1]','varchar(100)') AS CompanyName,
R.Node.query('.').value('(/Customers/City/.)[1]','varchar(100)') AS CityName
FROM @XML.nodes('/ROOT/Customers') R(Node);
Lowell
May 7, 2013 at 1:46 pm
Nice examples but they don't help me in my situation.
I have a table that has a text column. Inside the column is a XML stream.
Select top 1 message from surescripts_msg_import
where
message_type = 'REFILLREQUEST'
-- I get the following output:
<?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>
</Body></Message>
So my question is how do I run a query that will show me
ORDERS where NCPDPID=2356295
May 7, 2013 at 1:55 pm
mw112009 (5/7/2013)
Nice examples but they don't help me in my situation.I have a table that has a text column. Inside the column is a XML stream.
Actually Lowell's example shows you what you need to do. The problem is that you have a text column holding xml data. Why not just change the datatype to xml? FWIW, the text datatype is deprecated and shouldn't be used anymore.
You have a couple choices, you can either convert the data to xml in a table, cte or view. You can convert the original column to xml. If you chose either of those you can utilize the XML query that Lowell demonstrated.
The other option is to use charindex type of searches. The problem here is two fold. You can't use charindex on the text datatype so you would still have to convert this to a varchar datatype. Secondly you can't be certain that if you find a match it is for the correct element.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2013 at 2:02 pm
Actually, the "xml" you posted is still only pseudo XML. It is not legitimate XML. It is missing the closing tags for Pharmacy and RefillRequest.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2013 at 2:11 pm
ok here's a slightly modified version, showing exactly your problem, with a text field that must be converted;
i had to generate fake data, and assume a table structure, because you didn't provide anything:
CREATE TABLE [dbo].[surescripts_msg_import] (
[ID] INT IDENTITY(1,1) NOT NULL,
[message] TEXT NULL,
CONSTRAINT [PK__surescri__3214EC27133DC8D4] PRIMARY KEY CLUSTERED ([ID]) )
INSERT INTO surescripts_msg_import(message)
--some valud xml as an example
SELECT top 3
CONVERT(VARCHAR(max),decp.query_plan )
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp
WHERE decp.query_plan IS NOT NULL
SELECT ID,CONVERT(xml,message) FROM [surescripts_msg_import]
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT *,
DatabaseName = MyAlias.ConvertedXML.value(N'(//sp:RelOp/sp:OutputList/sp:ColumnReference/@Database)[1]', 'NVARCHAR(256)'),
TableName = MyAlias.ConvertedXML.value(N'(//sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Table)[1]', 'NVARCHAR(256)'),
SchemaName = MyAlias.ConvertedXML.value(N'(//sp:RelOp/sp:OutputList/sp:ColumnReference/@Schema)[1]', 'NVARCHAR(256)'),
ColumnName = MyAlias.ConvertedXML.value(N'(//sp:RelOp/sp:OutputList/sp:ColumnReference/@Column)[1]', 'NVARCHAR(256)'),
Alias = MyAlias.ConvertedXML.value(N'(//sp:RelOp/sp:OutputList/sp:ColumnReference/@Alias)[1]', 'NVARCHAR(256)')
FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]
)MyAlias
Lowell
May 8, 2013 at 9:06 am
i couldn't get your one example to work, because the data was malformed in your pasted example; it was missing two tags, so it was not valid xml; it was missing </Pharmacy></RefillRequest> tags
however, i wanted to at least be able to get it to work, so with some peer support in this thread, grab the example and see if it works for you in a larger dataset:
http://www.sqlservercentral.com/Forums/Topic1450578-21-1.aspx
Lowell
May 8, 2013 at 12:32 pm
Lets make this simple.
select top 100 message from [surescripts_msg_import]
In this case the column message is of type text.
Can someone please help me write a select query that will retrieve the value within the <address>....</address> tables please ?
If the xml is not well formed please use your imagination and make up a
xml stream that you would like.
May 8, 2013 at 12:34 pm
Sorry I meant the value between the <address>....</address> tags
May 8, 2013 at 12:41 pm
mw112009 (5/8/2013)
Lets make this simple.select top 100 message from [surescripts_msg_import]
In this case the column message is of type text.
Can someone please help me write a select query that will retrieve the value within the <address>....</address> tables please ?
If the xml is not well formed please use your imagination and make up a
xml stream that you would like.
you would need to paste two or three rows of valid sample XML, removing any personally identifiable/sensitive information;
I've put examples out there, but you seem to need a bit more precision and a bit less theory, so if you can provide the xml, i can put it in the sample table and shred it the way i did int he post i linked to.
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 *,
RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/*:Message/*:Body/*:RefillRequest/*:RxReferenceNumber/text()) [1]', 'NVARCHAR(256)'),
PrescriberOrderNumber = MyAlias.ConvertedXML.value(N'(/*:Message/*:Body/*:RefillRequest/*:PrescriberOrderNumber/text()) [1]', 'NVARCHAR(256)'),
NCPDPID = MyAlias.ConvertedXML.value(N'(/*:Message/*:Body/*:RefillRequest/*:Pharmacy/*:Identification/*:NCPDPID/text()) [1]', 'NVARCHAR(256)'),
StoreName = MyAlias.ConvertedXML.value(N'(/*:Message/*:Body/*:RefillRequest/*:Pharmacy/*:StoreName/text()) [1]', 'NVARCHAR(256)'),
Pharmacist = MyAlias.ConvertedXML.value(N'(/*:Message/*:Body/*:RefillRequest/*:Pharmacy/*:Pharmacist/*:LastName/text()) [1]', 'NVARCHAR(256)')
FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]
)MyAlias
Lowell
May 8, 2013 at 12:57 pm
Or since your XML appears to not really be xml maybe you need to query this as text.
if OBJECT_ID('tempdb..#XMLish') is not null
drop table #XMLish
create table #XMLish
(
Messaging text
)
insert #XMLish
select '<?xml version="1.0" encoding="utf-8"?><Message version="4.21" xmlns="http://www.surescripts.com/messaging"><Header>....<Address><AddressLine1>1991 E APPLE AVE</AddressLine1><City>MUSKEGON</City></Address>'
select * from #XMLish
select substring(CAST(Messaging as nvarchar(max)), CHARINDEX('<Address>', CAST(Messaging as nvarchar(max))) + 9, CHARINDEX('</Address>', CAST(Messaging as nvarchar(max))) - CHARINDEX('<Address>', CAST(Messaging as nvarchar(max))) - 9)
from #XMLish
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2013 at 12:57 pm
Thank you worked perfectly!
Great!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply