XML - Basic but help me out if you can

• NicHopper

  SSCrazy Eights

  Points: 9090

  More actions

  July 12, 2017 at 8:04 am

  #327360

  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

  
  

  Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  July 12, 2017 at 8:12 am

  #1950542

  Hi Nic,
  can you attach the XML as a text please?
  😎
  Quick thought, add "[1]" as a singleton directive.

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  July 12, 2017 at 3:31 pm

  #1950650

  NicHopper - Wednesday, July 12, 2017 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

  
  

  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

• NicHopper

  SSCrazy Eights

  Points: 9090

  More actions

  July 13, 2017 at 2:42 am

  #1950693

  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

  Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Thom A

  SSC Guru

  Points: 99126

  More actions

  July 13, 2017 at 2:56 am

  #1950694

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  July 13, 2017 at 3:40 am

  #1950704

  NicHopper - Thursday, July 13, 2017 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

  Quick question, will you only have one "session" element in each of the XML records?
  😎

• NicHopper

  SSCrazy Eights

  Points: 9090

  More actions

  July 18, 2017 at 2:53 am

  #1951321

  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

  Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  July 18, 2017 at 3:25 am

  #1951326

  NicHopper - Tuesday, July 18, 2017 2:53 AM

  Eirikur, I believe that there will only be one session element.

  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.
  😎