Inserting XML into table - 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 2008 Forums

Transact-SQL (2008)

Inserting XML into table

Author

Topic

jyothi_jayanth
Starting Member

7 Posts

Posted - 2010-07-13 : 22:41:18

here is the XML

set @inXML= '<UploadCategoryDetail CategoryName="Reservations">
<CategoryColumns>
<CategoryColumn ColumnName="DocumentID" Value="" />
<CategoryColumn ColumnName="DocumentFileName" Value="Invoice.pdf" />
<CategoryColumn ColumnName="DocumentFilePath" Value="\2009\November\3\15abb3a2-1ba2-4385-b41c-8a05dacfb9ca.pdf" />
</CategoryColumns>
</UploadCategoryDetail>'

i want into insert into table as follows

select DocumentID,DocumentFilename,DocumentFilePath from @XMLTempTable

Thanks

Jayanthu Babu gadde

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-14 : 03:21:38

[code]declare @inXML XML
set @inXML= '
<UploadCategoryDetail CategoryName="Reservations">
<CategoryColumns>
<CategoryColumn ColumnName="DocumentID" Value="" />
<CategoryColumn ColumnName="DocumentFileName" Value="Invoice.pdf" />
<CategoryColumn ColumnName="DocumentFilePath" Value="\2009\November\3\15abb3a2-1ba2-4385-b41c-8a05dacfb9ca.pdf" />
</CategoryColumns>
</UploadCategoryDetail>'

-- Display the result from a variable
SELECT MAX(CASE WHEN n.value('@ColumnName', 'VARCHAR(MAX)') = 'DocumentID' THEN n.value('@Value', 'VARCHAR(MAX)') ELSE NULL END) AS DocumentID,
MAX(CASE WHEN n.value('@ColumnName', 'VARCHAR(MAX)') = 'DocumentFilename' THEN n.value('@Value', 'VARCHAR(MAX)') ELSE NULL END) AS DocumentFilename,
MAX(CASE WHEN n.value('@ColumnName', 'VARCHAR(MAX)') = 'DocumentFilePath' THEN n.value('@Value', 'VARCHAR(MAX)') ELSE NULL END) AS DocumentFilePath
FROM @inXML.nodes('/UploadCategoryDetail/CategoryColumns/CategoryColumn') AS a(n)

DECLARE @XMLTempTable TABLE
(
RowID INT,
inXML XML
)

INSERT @XMLTempTable
SELECT 1, @inXML

-- Display the result from a table
SELECT MAX(CASE WHEN n.value('@ColumnName', 'VARCHAR(MAX)') = 'DocumentID' THEN n.value('@Value', 'VARCHAR(MAX)') ELSE NULL END) AS DocumentID,
MAX(CASE WHEN n.value('@ColumnName', 'VARCHAR(MAX)') = 'DocumentFilename' THEN n.value('@Value', 'VARCHAR(MAX)') ELSE NULL END) AS DocumentFilename,
MAX(CASE WHEN n.value('@ColumnName', 'VARCHAR(MAX)') = 'DocumentFilePath' THEN n.value('@Value', 'VARCHAR(MAX)') ELSE NULL END) AS DocumentFilePath
FROM @XMLTempTable AS f
CROSS APPLY f.inXML.nodes('/UploadCategoryDetail/CategoryColumns/CategoryColumn') AS a(n)
WHERE f.RowID = 1[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources