Xquery striping XML having problems separating records

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » XML » Xquery striping XML having problems...

Xquery striping XML having problems separating records

Rate Topic

Display Mode

Topic Options

Author

Message

ShineBoy

Posted Wednesday, November 28, 2012 5:12 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 5:31 AM
Points: 17, Visits: 44

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

Mark-101232

Posted Wednesday, November 28, 2012 5:20 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 1,500, Visits: 18,185

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

ShineBoy

Posted Tuesday, December 11, 2012 6:54 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 5:31 AM
Points: 17, Visits: 44

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 »

Permissions