Reading XML file

Question

Post reply

Reading XML file

SqlSavvy

SSCrazy

Points: 2641
More actions
March 19, 2009 at 3:23 pm

#155444

I have a XML file. How can I extract the layout of the file using Sql?
THANKS MUCH IN ADVANCE?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Yes you can load xml files:

DECLARE @xml XML

SELECT @xml = BulkColumn FROM OPENROWSET(

BULK 'C:\Users\Flo\Temp\Test\test.xml',

SINGLE_BLOB) AS x

SELECT @xml

Greets

Flo

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Have a look at http://msdn.microsoft.com/en-us/library/ms191184.aspx

SqlSavvy SSCrazy Points: 2641 More actions · Answer 3

This one just loads the entire file into one line. I need to be able to extract the exact columns so that I can give the file layout to my manager.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

You can use OPENXML to use XPath for data access.

Sample from BOL:

DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc ='

<ROOT>

<Customer CustomerID="VINET" ContactName="Paul Henriot">

<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"

OrderDate="1996-07-04T00:00:00">

<OrderDetail ProductID="11" Quantity="12"/>

<OrderDetail ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

<OrderDetail ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- SELECT stmt using OPENXML rowset provider

SELECT *

FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)

WITH (OrderID int '../@OrderID',

CustomerID varchar(10) '../@CustomerID',

OrderDate datetime '../@OrderDate',

ProdID int '@ProductID',

Qty int '@Quantity')

Greets

Flo

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Though in SQL 2005 you are probably better off using the XML data type and methods.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Hi Barry

Sorry for this... I'm currently on my track away from SQL Server 2000.

Just had a look for the new syntax:

DECLARE @xml XML

SELECT @xml = '<root><item>value1</item><item>value2</item></root>'

SELECT c.query('text()')

FROM @xml.nodes('root/item') T(c)

Greets

Flo

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Good stuff both ways, Florian.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

SqlSavvy SSCrazy Points: 2641 More actions · Answer 8

I am still not getting it...

Here is my XML..Can somebody tell me how to get the layout from it using sql?

-

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

You cannot just drop raw XML into a forum post. Either wrap it in [ code ] tags or attach it as a txt or zip file.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 10

RBarryYoung (3/19/2009)
Good stuff both ways, Florian.

Thanks Barry! For review and feedback. 🙂