Loading xml into XML variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Loading xml into XML variable

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-08-06 : 07:23:58

1)how can i load from file and xml to and XML variable?
where i mean :
Declare @x1 XML
@x1=loadfile(1.xml")
2)how can i go over the main nodes of the xml,take them and send them as an xml to a STORED PROCEDURE?
if this is the xml :
<a>
<c>...</c>...
...
...
</a>
i want to send each "" to a STROED PROCEDURE
Thanks
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 07:36:58

See this blog post how to read an XML file into a SQL Server XML datatype
http://weblogs.sqlteam.com/peterl/archive/2009/03/05/Extract-XML-structure-automatically.aspx

quote:

-- Initialize command string, return code and file content 
DECLARE @cmd NVARCHAR(MAX), 
        @rc INT, 
        @Data XML 

-- Make sure accents are preserved if encoding is missing by adding encoding information UTF-8 
SET     @cmd = 'SELECT  @Content = CASE 
                                       WHEN BulkColumn LIKE ''%xml version="1.0" encoding="UTF%'' THEN BulkColumn 
                                       ELSE ''<?xml version="1.0" encoding="UTF-8"?>'' + BulkColumn 
                                   END 
                FROM    OPENROWSET(BULK ' + QUOTENAME(@FileName, '''') + ', SINGLE_CLOB) AS f' 

-- Read the file 
EXEC    @rc = sp_executesql @cmd, N'@Content XML OUTPUT', @Content = @Data OUTPUT

And see my previous response to your question here how to get all "b" elements whereever they are.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130660

quote:

SELECT	n.value('.', 'INT') AS theValue
FROM	@Data.nodes('//b') AS r(n)

N 56°04'39.26"
E 12°55'05.63"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-08-06 : 07:47:55

1) thanks for the file load link.
2)the code sample you gave me gives me a result in a table, while i need the entire node as an xml of itself, so i will send it to a stored procedure,something like
for each nd in node
Exec sp_xxx node
next

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 08:25:24

[code]DECLARE @FileName NVARCHAR(300)

SET @FileName = '\\MyUncPathHere\MyFileNameHere.xml'

-- Initialize command string, return code and file content
DECLARE @cmd NVARCHAR(MAX),
@rc INT,
@Data XML

-- Make sure accents are preserved if encoding is missing by adding encoding information UTF-8
SET @cmd = 'SELECT @Content = CASE
WHEN BulkColumn LIKE ''%xml version="1.0" encoding="UTF%'' THEN BulkColumn
ELSE ''<?xml version="1.0" encoding="UTF-8"?>'' + BulkColumn
END
FROM OPENROWSET(BULK ' + QUOTENAME(@FileName, '''') + ', SINGLE_NCLOB) AS f'

-- Read the file and get the content in a XML variable
EXEC @rc = sp_executesql @cmd, N'@Content XML OUTPUT', @Content = @Data OUTPUT

-- Stage the "b"-elements
DECLARE @Stage TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
b XML NOT NULL
)

INSERT @Stage
(
b
)
SELECT r.n.query('.')
FROM @Sample.nodes('/a/b') AS r(n)

DECLARE @CurrID INT,
@MaxID INT

SELECT @CurrID = 1,
@MaxID = MAX(RowID)
FROM @Stage

WHILE @CurrID < @MaxID
BEGIN
SELECT @Data = n
FROM @Stage
WHERE RowID = @CurrID

EXEC sp_xxx @Data

SET @CurrID = @CurrID + 1
END[/code]

N 56°04'39.26"
E 12°55'05.63"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-08-06 : 08:29:38

i see thans alot!
i did chamged SINGLE_CLOB to SINGLE_NCLOB beacuse of nvarchar file (unicode)

thanks!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources