August 31, 2007 at 12:25 pm
Hello,
Say I have an xml sample:
<root>
<location>
<header>
<id>1</id>
</header>
<graphic>
<id>2</id>
<text>
<id>3</id>
<type>
<home>abc</home>
<work>def</work>
</type>
</text>
<text>
<id>4</id>
<type indent=1>
<home>qrs</home>
<work>tuv</work>
</type>
</text>
<text>
<id>5</id>
<bold count=12>
<home>klm</home>
<work>nop</work>
</type>
</text>
<comment>
<id>6</d>
</comment>
</location>
</root>
I want to get a row for each child tag of <location>, like
headerid graphicid id home work type_indent bold
----------------------------------------------------------------------------------------------------
1 null null null null null null
null 2 null null null null null
null null 3 abc def null null
null null 4 qrs tuv 1 null
null null 5 klm nop null 12
null null 6 null null null null
I need to identify when the id is the "header" or the "graphicid", but the other id values can be placed under the "id" column. Also, I need to get the values of the indent attribute an bold attribute if there is any and there won't always be. I'm not having much luck getting the above result.
Here is my script. I'm using SQL Server 2005:
DECLARE
@xmlLocations XML
SELECT
@xmlLocations = BulkColumn
FROM
OPENROWSET(BULK 'C:\locations.xml', SINGLE_BLOB) AS x
select
x.location.value( 'header[1]/Id[1]', 'varchar(100)') AS HeaderID
x.location.value( 'graphicid[1]/Id[1]', 'varchar(100)') AS graphicID,
x.location.value( 'Id[1]', 'varchar(100)') AS ID,
x.location.value( 'Type[1]/Home[1]', 'varchar(100)') AS Home,
x.location.value( 'Type[1]/Work[1]', 'varchar(100)') AS Work,
x.location.value( 'TEXT[1]/Type[@Indent]', 'int') AS Type_Indent,
x.location.value( 'TEXT[1]/BoldText[@End]', 'int') AS BoldText
FROM
@xmlLocations.nodes('Root/location/*')
AS
x ( location )
Any assistance would be appreciated!
September 4, 2007 at 7:18 am
You can use UNION to get the result. The following is the sample code:
DECLARE @xmlLocations XML
SELECT
@xmlLocations = BulkColumn
FROM
OPENROWSET(BULK 'C:\locations.xml', SINGLE_BLOB) AS x
select
x.location.value( 'header[1]/Id[1]', 'varchar(100)') AS HeaderID,
NULL as graphicID,NULL as ID, NULL as Home, NULL as Work, NULL as Type_Indent, NULL as BoldText
FROM @xmlLocations.nodes('Root/location/*')
AS
x ( location )
UNION ALL
NULL,
x.location.value( 'graphicid[1]/Id[1]', 'varchar(100)') AS graphicID,
NULL,NULL,NULL,NULL,NULL
FROM @xmlLocations.nodes('Root/location/*')
AS
x ( location )
UNION ALL
SELECT NULL,NULL,
x.location.value( 'Id[1]', 'varchar(100)') AS ID,
x.location.value( 'Type[1]/Home[1]', 'varchar(100)') AS Home,
x.location.value( 'Type[1]/Work[1]', 'varchar(100)') AS Work,
x.location.value( 'TEXT[1]/Type[@Indent]', 'int') AS Type_Indent,
x.location.value( 'TEXT[1]/BoldText[@End]', 'int') AS BoldText
FROM
@xmlLocations.nodes('Root/location/*')
AS
x ( location )
SELECT NULL,NULL,
x.location.value( 'comment[1]/Id[1]', 'varchar(100)') AS ID,
NULL,NULL,NULL,NULL
FROM @xmlLocations.nodes('Root/location/*')
AS
x ( location )
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply