Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Xquery striping XML having problems separating records Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 5:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:01 PM
Points: 19, Visits: 64
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:

cf36ef98zzzzz98 Skyops LtdSpiedonme Ltd
cf36ef98zzzzz98 Skyops LtdSpiedonme Ltd

--Basically bunched to gether.
--I want something more like:
cf36ef98 Skyops Ltd
zzzzz98 Spiedonme 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.

Post #1389651
Posted Wednesday, November 28, 2012 5:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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)



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1389654
Posted Tuesday, December 11, 2012 6:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:01 PM
Points: 19, Visits: 64
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.
Post #1395390
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse