August 3, 2015 at 12:17 pm
I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column(MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.
Sample Table Data
MessageContentType | BodySegment
xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....
Current Query - HardCode Script
SELECT
ID,MsgContentType
BODYSEGMENT,
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan
FROM
s
When i tried the below line of script, I'm getting this error "The argument 1 of the XMLdata type method "value" must be a string literal."
Concat MsgContentType Column
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
To overcome that error i used sql column but I'm getting this error XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test".
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
Please guide me on this issue. Tanks
___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂
August 3, 2015 at 1:06 pm
Thanks Guy. I came up with my own solution by replacing those content with my custom string by using the string replace function.
It will more helpful if you share the proper procedure to get those XML nodes values in generic way.
___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂
August 3, 2015 at 5:21 pm
declare @x xml = '<ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">test</ns0:ADT_A03_26_GLO_DEF>';
declare @t table(col sysname);
insert @t values('ADT_A03_26_GLO_DEF');
with xmlnamespaces(default 'http://microsoft.com/HealthCare/HL7/2X')
select *, @x.value('(//*[local-name() = sql:column("col")]/text())[1]','varchar(100)')
from @t
If you declare the namespace as default, then it will work.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 4, 2015 at 2:01 pm
Thanks for your reply, the value int the parent node "ADT_A03_26_GLO_DEF" will vary for each and every record.
___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂
August 4, 2015 at 4:40 pm
windows_mss (8/4/2015)
Thanks for your reply, the value int the parent node "ADT_A03_26_GLO_DEF" will vary for each and every record.
That's no problem, just post some sample data and I'll show you...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 5, 2015 at 9:09 am
Thanks mister.magoo, please find the sample XML structure for 2 records in the table,I'm trying to read those nodes value in select clause.
Record 1
<ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">
<EVN_EventType>
<EVN_1_EventCode>A03</EVN_1_EventCode>
<EVN_2_DateTime>20100528144503</EVN_2_DateTime>
</EVN_EventType>
.
.
.
</<ns0:ADT_A03_26_GLO_DEF>
Record 2
<ns0:ADT_A04_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">
<EVN_EventType>
<EVN_1_EventCode>A03</EVN_1_EventCode>
<EVN_2_DateTime>20100528144503</EVN_2_DateTime>
</EVN_EventType>
.
.
.
</<ns0:ADT_A04_26_GLO_DEF>
___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂
August 5, 2015 at 9:13 am
Please read the link in my signature "Forum Etiquette: How to post data/code on a forum to get the best help"
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 5, 2015 at 9:18 am
Thanks mister.magoo for your post suggestion, I will correct it and share the updated content shortly.
___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂
August 5, 2015 at 9:28 am
Please find the sample data,
DECLARE @tmpTbl TABLE
(
ID int,
BodySegment xml
)
INSERT INTO @tmpTbl (ID,BodySegment) VALUES
(1,'<ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">
<EVN_EventType>
<EVN_1_EventTypeCode>A03</EVN_1_EventTypeCode>
<EVN_2_RecordedDateTime>19000101144602</EVN_2_RecordedDateTime>
<EVN_3_DateTimePlannedEvent>19000101144602</EVN_3_DateTimePlannedEvent>
<EVN_4_EventReasonCode>02</EVN_4_EventReasonCode>
</EVN_EventType>
</ns0:ADT_A03_26_GLO_DEF>')
INSERT INTO @tmpTbl (ID,BodySegment) VALUES
(2,'<ns0:ADT_A04_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">
<EVN_EventType>
<EVN_1_EventTypeCode>A04</EVN_1_EventTypeCode>
<EVN_2_RecordedDateTime>19000101144602</EVN_2_RecordedDateTime>
<EVN_3_DateTimePlannedEvent>19000101144602</EVN_3_DateTimePlannedEvent>
<EVN_4_EventReasonCode>02</EVN_4_EventReasonCode>
</EVN_EventType>
</ns0:ADT_A04_26_GLO_DEF>')
SELECT * FROM @tmpTbl
___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂
August 5, 2015 at 9:49 am
Thanks, but I thought you wanted to specify a node name as well?
Otherwise, you could just take node 1?
Edit:Much later, with no further input, I will offer this suggestion:
DECLARE @tmpTbl TABLE
(
ID int,
NodeName nvarchar(100),
BodySegment xml
)
INSERT INTO @tmpTbl (ID,NodeName,BodySegment) VALUES
(1,N'ADT_A03_26_GLO_DEF',N'<ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">
<EVN_EventType>
<EVN_1_EventTypeCode>A03</EVN_1_EventTypeCode>
<EVN_2_RecordedDateTime>19000101144602</EVN_2_RecordedDateTime>
<EVN_3_DateTimePlannedEvent>19000101144602</EVN_3_DateTimePlannedEvent>
<EVN_4_EventReasonCode>02</EVN_4_EventReasonCode>
</EVN_EventType>
</ns0:ADT_A03_26_GLO_DEF>')
INSERT INTO @tmpTbl (ID,NodeName,BodySegment) VALUES
(2,N'ADT_A04_26_GLO_DEF',N'<ns0:ADT_A04_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">
<EVN_EventType>
<EVN_1_EventTypeCode>A04</EVN_1_EventTypeCode>
<EVN_2_RecordedDateTime>19000101144602</EVN_2_RecordedDateTime>
<EVN_3_DateTimePlannedEvent>19000101144602</EVN_3_DateTimePlannedEvent>
<EVN_4_EventReasonCode>02</EVN_4_EventReasonCode>
</EVN_EventType>
</ns0:ADT_A04_26_GLO_DEF>');
-- If you actually have XML with multiple nodes at the root level
SELECT Id, NodeName, BodySegment
, BodySegment.value('(*[local-name()=sql:column("NodeName")]/EVN_EventType/EVN_2_RecordedDateTime)[1]','nvarchar(300)') TimeSpan
FROM @tmpTbl
-- If your real xml is as simple as the example, you don't need to know the node-name
SELECT Id, NodeName, BodySegment
, BodySegment.value('(*/EVN_EventType/EVN_2_RecordedDateTime)[1]','nvarchar(300)') TimeSpan
FROM @tmpTbl
It turns out that the XML sample you provided doesn't need the XML namespace declaration at all.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 6, 2015 at 9:41 am
Sorry mister.magoo, somehow i missed to hit the post reply button, i need to read all the child node of EVN_EventType. Thanks much.
___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply