InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 12:24:45 GMT20http://www.sqlservercentral.com/Forums/Topic1389651-21-1.aspxThanks 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.Tue, 11 Dec 2012 18:54:48 GMTShineBoyhttp://www.sqlservercentral.com/Forums/Topic1389651-21-1.aspxTry this[code="sql"]SELECT x.r.value('(Contact/ContactID/text())[1]','VARCHAR(40)') AS ContactID, x.r.value('(Contact/Name/text())[1]','VARCHAR(200)') AS NameFROM @Xml.nodes('/Response/Invoices/Invoice') AS x(r)[/code]Wed, 28 Nov 2012 05:20:05 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1389651-21-1.aspxGot a problem stripping XML, really just because I am lost on Xquery:Heres what I am doing:Declare @Xml XMLSet @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 CompanyNameFROM @xml.nodes('//ContactID') AS x(y)--This is where I just don't get it.My results are:cf36ef98zzzzz98 Skyops LtdSpiedonme Ltdcf36ef98zzzzz98 Skyops LtdSpiedonme Ltd--Basically bunched to gether.--I want something more like:cf36ef98 Skyops Ltdzzzzz98 Spiedonme Ltdwhat 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.Wed, 28 Nov 2012 05:12:34 GMTShineBoy