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 123»»»

Querying XML Expand / Collapse
Author
Message
Posted Friday, February 19, 2010 7:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 05, 2014 1:40 PM
Points: 147, Visits: 629
Hi everyone,

I have the following table:

CREATE TABLE [dbo].[XmlImportTest](
[xml_data] [xml] NULL
)

-- I add the following data in:
insert into dbo.XmlImportTest (xml_data)
values
('<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>')

I want the result to come out like this:

Customerid CompanyName
1111 Sean Chai
1112 Tom Johnston
1113 Institue of Art

I've looked at some OPENXML & FOR XML examples, but still can't get a grasp on how to write this query. Please help.

Thanks,
Post #869091
Posted Friday, February 19, 2010 8:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 4,052, Visits: 4,192
I believe that you want the FOR XML EXPLICIT.

Please check the following examples:

http://www.sqlservercentral.com/articles/ADO/article4/516/

http://articles.sitepoint.com/article/data-as-xml-sql-server

Please let me know how you make out.

Thanks.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #869111
Posted Friday, February 19, 2010 8:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:34 PM
Points: 15,442, Visits: 9,588
DECLARE @XML XML;

SELECT @XML =
'<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>';

SELECT --R.Node.query('.'),
R.Node.query('.').value('(/Customers/CustomerId/.)[1]','varchar(100)') AS CustomerID,
R.Node.query('.').value('(/Customers/CompanyName/.)[1]','varchar(100)') AS CompanyName
FROM @XML.nodes('/ROOT/Customers') R(Node);

You'll need to use your column name instead of the XML variable, but it should do what you want.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #869146
Posted Friday, February 19, 2010 9:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 05, 2014 1:40 PM
Points: 147, Visits: 629
Thank you very much!!!
Post #869191
Posted Sunday, February 21, 2010 12:13 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Welsh Corgi (2/19/2010)
I believe that you want the FOR XML EXPLICIT.

Welsh: Just an FYI, but "FOR XML EXPLICIT" should always be a last resort for XML construction, it is a truly brutal facility. Most things that you need can almost always be constructed with "FOR XML PATH", which is about 100x easier to use. And a little string manipulation can usually get the rest.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #869782
Posted Sunday, February 21, 2010 2:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 4,052, Visits: 4,192
Mr Young,

Thank you for setting me straight.

I appreciate & respect your input.

If you think of any instructional articles and if you get an chance I would appreciate if you would share them with me.

Regards,
WC


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #869799
Posted Sunday, February 21, 2010 3:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
The "XML Workshop" series of articles by Jacob Sebastian (search this site for details, please) is a great start and covers the import of xml data as well as formatting relational data in xml format.

One link as a start:
http://www.sqlservercentral.com/articles/Miscellaneous/2996/




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #869810
Posted Sunday, February 21, 2010 6:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 11,168, Visits: 10,927
GSquared (2/19/2010)
You'll need to use your column name instead of the XML variable, but it should do what you want.

A much more efficient query plan is produced from this code:

DECLARE @XML XML;

SET @XML =
N'
<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>';

SELECT DV.customer_id,
DV.company_name
FROM @XML.nodes('./ROOT/Customers')
AS T(customers)
CROSS
APPLY (
SELECT customers.value('(CustomerId/text())[1]', 'VARCHAR(100)'),
customers.value('(CompanyName/text())[1]', 'VARCHAR(100)')
)
AS DV (customer_id, company_name);

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #869834
Posted Sunday, February 21, 2010 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 05, 2014 1:40 PM
Points: 147, Visits: 629
Again, thank you again everyone.
Post #869836
Posted Monday, February 22, 2010 6:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:34 PM
Points: 15,442, Visits: 9,588
Paul White (2/21/2010)
GSquared (2/19/2010)
You'll need to use your column name instead of the XML variable, but it should do what you want.

A much more efficient query plan is produced from this code:

DECLARE @XML XML;

SET @XML =
N'
<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>';

SELECT DV.customer_id,
DV.company_name
FROM @XML.nodes('./ROOT/Customers')
AS T(customers)
CROSS
APPLY (
SELECT customers.value('(CustomerId/text())[1]', 'VARCHAR(100)'),
customers.value('(CompanyName/text())[1]', 'VARCHAR(100)')
)
AS DV (customer_id, company_name);

Paul


Clever. I hadn't thought of using Cross Apply at that point in the query. (Use it all the time in separating out the nodes. Just never have for the values.) I'll have to do some speed tests with that.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #870287
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse