August 10, 2010 at 8:25 pm
Hello, I'm working on a database which has a table with an xml column. I'm not able to query the column because a namespace is causing all queries to return a null value. I don't know how I should fix this problem. Right now I can't read or query any of the xml in this column. The offending line:
<document xmlns="urn:hl7-org:v3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:hl7-org:v3 http://www.accessdata.fda.gov/spl/schema/spl.xsd">
When I execute the following code;
declare @xut as xml;
select @xut = (select LabelXml from label where LabelId=11662)
select @xut.value ('(/document/title)[1]', 'varchar(MAX)') as title
select DATALENGTH(@xut) as xmlSize
xmlSize = 23773 and title is null.
When I remove xmlns="urn:hl7-org:v3" and re-run the query, xmlSize and title have the correct value.
Thank you to anyone who will help me,
...bob
August 11, 2010 at 5:17 am
please provide the sample xml data in total, not only the first line.
You'll need to use WITH XMLNAMESPACES() together with your select statement, and maybe you'd need to add the namespace to your nodes as well.
As a side note: there are most probably better performing ways to solve your problem... But like I stated before, I'd like to have some data to play with...
August 11, 2010 at 5:47 am
Thank you Lutz, I did add the namespaces and it returned the correct values;
;with xmlnamespaces(default 'urn:hl7-org:v3')
select Labelid,
LabelXml.value('(/document/title)[1]', 'varchar(50)') AS 'Title',
LabelXml.value('(/document/effectiveTime)[1]/@value', 'varchar(50)') as effectiveTime
My issue now is unfamiliarity with the xml field; If you would provide a sanity check for me and verify that I can do what I'd like.
I'd like to return all of the records in a table that have a certain value inside a node of a xml column. For example;
<actDefinition>
<code code="C43360" codeSystem="2.16.840.1.113883.3.26.1.1" displayName="Manufacture" />
</actDefinition>
Can I query a table for all the records in which the /document/actDefinition/code/@displayName xml node inside the xml field contains 'Manufacture' ?
Thank you very much for your help,
...bob
August 11, 2010 at 7:43 am
rscavilla (8/11/2010)
Thank you Lutz, I did add the namespaces and it returned the correct values;
;with xmlnamespaces(default 'urn:hl7-org:v3')select Labelid,
LabelXml.value('(/document/title)[1]', 'varchar(50)') AS 'Title',
LabelXml.value('(/document/effectiveTime)[1]/@value', 'varchar(50)') as effectiveTime
My issue now is unfamiliarity with the xml field; If you would provide a sanity check for me and verify that I can do what I'd like.
I'd like to return all of the records in a table that have a certain value inside a node of a xml column. For example;
<actDefinition><code code="C43360" codeSystem="2.16.840.1.113883.3.26.1.1" displayName="Manufacture" />
</actDefinition>
Can I query a table for all the records in which the /document/actDefinition/code/@displayName xml node inside the xml field contains 'Manufacture' ?
Thank you very much for your help,
...bob
Yes, you can!
There are several ways to do it, depending on the required result.
So, please provide sample data and expected result based on those sample data. The xml fractions you provided so far won't help much...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply