July 12, 2017 at 8:04 am
Hi,
This is probably pretty basic but I've never really played with queryingXML. I've attached a screenshot which shows my XML and I'm trying to recoverthe highlighted bits using;
--@X = the XML
SELECT@X.value('/sessiondata/import/session/username[1]','varchar(50)')
The issue is I'm not sure what to put in the empty string for the path,for example with the above I get this
XQuery[value()]: 'value()' requires a singleton (or empty sequence), found operand oftype 'xdt:untypedAtomic *'
Iām missing something here, could someone please give me some examplesof how I would get back the highlighted sections please?
Thanks for taking the time to read this.
Any help is appreciated.
Nic
July 12, 2017 at 8:12 am
Hi Nic,
can you attach the XML as a text please?
š
Quick thought, add "[1]" as a singleton directive.
July 12, 2017 at 3:31 pm
NicHopper - Wednesday, July 12, 2017 8:04 AMHi,
This is probably pretty basic but I've never really played with queryingXML. I've attached a screenshot which shows my XML and I'm trying to recoverthe highlighted bits using;
--@X = the XML
SELECT@X.value('/sessiondata/import/session/username[1]','varchar(50)')
The issue is I'm not sure what to put in the empty string for the path,for example with the above I get this
XQuery[value()]: 'value()' requires a singleton (or empty sequence), found operand oftype 'xdt:untypedAtomic *'
I’m missing something here, could someone please give me some examplesof how I would get back the highlighted sections please?
Thanks for taking the time to read this.
Any help is appreciated.
Nic
You have two options:
/* Use parens to apply the [1] directive to the entire path. */
SELECT@X.value('(/sessiondata/import/session/username)[1]','varchar(50)')
/* Apply the [1] directive to each element in the path. */
SELECT@X.value('/sessiondata[1]/import[1]/session[1]/username[1]','varchar(50)')
Obviously you could do a combination of both of these, but sticking to one method improves legibility.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 13, 2017 at 2:42 am
Hi,
I've attached a sample file, the actual XML is large and sensitive in nature but using the sample if I can get the following then I should be able to apply the same logic on the proper XML
The date modified at the top.
Broker state.
The system id at the bottom.
Thanks,
Nic
July 13, 2017 at 2:56 am
Perhaps this helps:CREATE TABLE #Sample (X xml);
GO
INSERT INTO #Sample
VALUES (
'<session id="342338">
<properties dateModified="2015-02-27" manuscript="Test_1_0_0_0" engineVersion="2.0.0" cultureCode="en-US" cultureName="United States [english]" caption="Test" context="Test">
<userName>tester</userName>
</properties>
<Broker>
<CompanyName>Microsoft</CompanyName>
<Address1>Thames Valley Park</Address1>
<Address2 />
<City>Reading</City>
<State>Berkshire</State>
<ZipCode>RG6 1WG</ZipCode>
<Country />
</Broker>
<system id="s2CE8879D3D974104A758D58FAA83580B">
<DateTimeCurrentDate>2014-10-09</DateTimeCurrentDate>
<ContextActiveUserName>Test</ContextActiveUserName>
</system>
</session>');
GO
SELECT X.value('(/session/@id)[1]','int') AS SessionID, --Use the @ operator here, as id is a property
X.value('(session/properties/userName/text())[1]','varchar(50)') AS username, --Use text() here, as userName is a node.
X.value('(session/Broker/CompanyName/text())[1]','varchar(50)') AS BrokerCompany,
X.value('(/session/system/@id)[1]','varchar(50)') AS SystemID
FROM #Sample S;
GO
DROP TABLE #Sample;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 13, 2017 at 3:40 am
NicHopper - Thursday, July 13, 2017 2:42 AMHi,I've attached a sample file, the actual XML is large and sensitive in nature but using the sample if I can get the following then I should be able to apply the same logic on the proper XML
The date modified at the top.
Broker state.
The system id at the bottom.Thanks,
Nic
Quick question, will you only have one "session" element in each of the XML records?
š
July 18, 2017 at 2:53 am
Hi,
Thom, thanks for the samples, makes thing clearer in my mind.
Eirikur, I believe that there will only be one session element.
Thanks to all those that read this post.
Thanks,
Nic
July 18, 2017 at 3:25 am
Do you have the XSD for the XML? Unless the number of session instances are limited within the XSD, I would use the nodes method in case there will be multiple instances.
š
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply