Xquery striping XML having problems separating records

• ShineBoy

  Right there with Babe

  Points: 764

  More actions

  November 28, 2012 at 5:12 am

  #104180

  Got a problem stripping XML, really just because I am lost on Xquery:

  Heres what I am doing:

  Declare @Xml XML

  Set @Xml = '<Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Id>8991f860-c787-41bc-93d6-09f496f75169</Id>

  <Status>OK</Status>

  <ProviderName>API Previewer</ProviderName>

  <DateTimeUTC>2012-11-28T09:14:12.2077238Z</DateTimeUTC>

  <Invoices>

  <Invoice>

  <Contact>

  <ContactID>cf36ef98</ContactID>

  <ContactStatus>ACTIVE</ContactStatus>

  <Name>Skyops Ltd</Name>

  </Contact>

  <Date>2012-11-15T00:00:00</Date>

  <DueDate>2012-12-20T00:00:00</DueDate>

  <Status>AUTHORISED</Status>

  <LineAmountTypes>Exclusive</LineAmountTypes>

  <LineItems>

  <LineItem>

  <Description>For Full ColourAdvertisment in 2012</Description>

  </LineItem>

  </LineItems>

  <SubTotal>819.00</SubTotal>

  <TotalTax>122.85</TotalTax>

  <Total>941.85</Total>

  <UpdatedDateUTC>2012-11-28T09:14:11.003</UpdatedDateUTC>

  <CurrencyCode>NZD</CurrencyCode>

  </Invoice>

  <Invoice>

  <Contact>

  <ContactID>zzzzz98</ContactID>

  <ContactStatus>ACTIVE</ContactStatus>

  <Name>Spiedonme Ltd</Name>

  </Contact>

  </Invoice>

  </Invoices>

  </Response>'

  SELECT @xml.query('/Response/Status').value('.','varchar(100)')

  -- results from this one good, all I want to do is ensure that I get an 'OK'

  SELECT @xml.query('//ContactID[1]').value('.','varchar(40)') as ContactId,

  @xml.query('//Name[1]').value('.','varchar(200)') as CompanyName

  FROM @xml.nodes('//ContactID') AS x(y)

  --This is where I just don't get it.

  My results are:

  cf36ef98zzzzz98Skyops LtdSpiedonme Ltd

  cf36ef98zzzzz98Skyops LtdSpiedonme Ltd

  --Basically bunched to gether.

  --I want something more like:

  cf36ef98 Skyops Ltd

  zzzzz98Spiedonme Ltd

  what am I doing wrong?

  Ideally I want to put the results of the second query into a table so I can do more with it, But I reckon I can figure out how to do that , once I get the results in the right format.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  November 28, 2012 at 5:20 am

  #1563038

  Try this

  SELECT x.r.value('(Contact/ContactID/text())[1]','VARCHAR(40)') AS ContactID,

  x.r.value('(Contact/Name/text())[1]','VARCHAR(200)') AS Name

  FROM @Xml.nodes('/Response/Invoices/Invoice') AS x(r)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• ShineBoy

  Right there with Babe

  Points: 764

  More actions

  December 11, 2012 at 6:54 pm

  #1567671

  Thanks heaps, that works well.

  Haven't done much previously with XML data in T-SQL, but once you get the hang of it, it fast and easy enough, just mind numbing when you write For xml explicit queries.