Xquery -- Select not returning all the attributes

Question

Xquery -- Select not returning all the attributes

mail4bbt

SSC-Addicted

Points: 416
More actions
January 29, 2013 at 2:33 pm

#288560

I have a table:
CREATE TABLE [dbo].[XmlTable](
[XmlId] [int] IDENTITY(1,1) NOT NULL,
[XmlDocument] [xml] NOT NULL,
CONSTRAINT [PK_XmlTable] PRIMARY KEY CLUSTERED
(
[XmlId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
With a schema structure: stored in the xml column
<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Settings="Testing" Title="Ordering">
<dev:Base RevisionNumber="0" Localid="E43D7" />
<dev:Rev Time="2013-01-21T15:08:00">
<dev:Person Name="Me" Systemid="54654" />
</dev:Rev>
<dev:Functions Id="A1">
<dev:A1 Number="1">
<dev:Codes>D</dev:Codes>
<dev:Required>true</dev:Required>
<dev:Informational>false</dev:Informational>
<dev:Visitors>
<dev:Visitor Name="Dev01" Location="STLRF">
<dev:Divisions>
<dev:Division Number="1" Name="TFR3" Usage="Monitor">
<dev:Description>Development Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="2" Name="DEF32" Usage="Monitor">
<dev:Description>Testing Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="3" Name="DEP13" Usage="None">
<dev:Description>Guided Fundamentals</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
<dev:Visitor Name="Dev02" Location="STLRF">
<dev:Divisions>
<dev:Division Number="1" Name="TFR3" Usage="Monitor">
<dev:Description>Development Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="2" Name="DEF32" Usage="Monitor">
<dev:Description>Testing Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="3" Name="DEP13" Usage="None">
<dev:Description>Guided Fundamentals</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
<dev:Visitor Name="Dev03" Location="FGRTY">
<dev:Divisions>
<dev:Division Number="1" Name="TFR3" Usage="Monitor">
<dev:Description>Development Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="2" Name="DEF32" Usage="Monitor">
<dev:Description>Testing Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="3" Name="DEP13" Usage="None">
<dev:Description>Guided Fundamentals</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
</dev:Visitors>
</dev:A1>
</dev:Functions>
</dev:Doc>
I am trying to return the id, number, name, and location of the visitors
Something like:
A1 1 Dev01 STLRF
A1 1 Dev02 STLRF
A1 1 Dev03 FGRTY
;WITH XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema' AS dev )
SELECT
a.c.value('@Number', 'INT') [Number],
a.c.value('(dev:Visitors/dev:Visitor/@Name)[1]', 'VARCHAR(100)') Visitor
FROM XmlTable AS X
CROSS APPLY X.Xmldocument.nodes('dev:Doc/dev:Functions/dev:A1') a(c)
GO

Viewing 5 posts - 1 through 4 (of 4 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

It may just be the pathing. I've broken the XQUERY component out here directly as a local doc. See if this fixes the issue, else we'll have to dig into the problem you're getting from the table side. I just want to make sure it's not a syntax thing first since that's the most common issue with XQUERY.

DECLARE @xml XML,

@dochandle INT

SET @xml = '<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Settings="Testing" Title="Ordering">

<dev:Base RevisionNumber="0" Localid="E43D7" />

<dev:Rev Time="2013-01-21T15:08:00">

<dev:Person Name="Me" Systemid="54654" />

</dev:Rev>

<dev:Functions Id="A1">

<dev:A1 Number="1">

<dev:Codes>D</dev:Codes>

<dev:Required>true</dev:Required>

<dev:Informational>false</dev:Informational>

<dev:Visitors>

<dev:Visitor Name="Dev01" Location="STLRF">

<dev:Divisions>

<dev:Division Number="1" Name="TFR3" Usage="Monitor">

<dev:Description>Development Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="2" Name="DEF32" Usage="Monitor">

<dev:Description>Testing Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="3" Name="DEP13" Usage="None">

<dev:Description>Guided Fundamentals</dev:Description>

</dev:Division>

</dev:Divisions>

</dev:Visitor>

<dev:Visitor Name="Dev02" Location="STLRF">

<dev:Divisions>

<dev:Division Number="1" Name="TFR3" Usage="Monitor">

<dev:Description>Development Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="2" Name="DEF32" Usage="Monitor">

<dev:Description>Testing Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="3" Name="DEP13" Usage="None">

<dev:Description>Guided Fundamentals</dev:Description>

</dev:Division>

</dev:Divisions>

</dev:Visitor>

<dev:Visitor Name="Dev03" Location="FGRTY">

<dev:Divisions>

<dev:Division Number="1" Name="TFR3" Usage="Monitor">

<dev:Description>Development Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="2" Name="DEF32" Usage="Monitor">

<dev:Description>Testing Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="3" Name="DEP13" Usage="None">

<dev:Description>Guided Fundamentals</dev:Description>

</dev:Division>

</dev:Divisions>

</dev:Visitor>

</dev:Visitors>

</dev:A1>

</dev:Functions>

</dev:Doc>'

EXEC sp_xml_prepareDocument @dochandle OUTPUT, @xml, '<root xmlns:dev="http://www.w3.org/2001/XMLSchema"/>'

SELECT

*

FROM

OPENXML(@dochandle, '/dev:Doc/dev:Functions/dev:A1/dev:Visitors/dev:Visitor', 1)

WITH ( Id VARCHAR(10) '../../../@Id',

Number VARCHAR(10) '../../@Number',

Name VARCHAR(20),

Location VARCHAR(20)

)

EXEC sp_xml_removeDocument @dochandle

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mail4bbt SSC-Addicted Points: 416 More actions · Answer 2

That is exactly what I needed to be returned. Im gonna compare it to the value and query method I was trying.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 3

Here's another to try

WITH XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema' AS dev )

SELECT

a.c.value('local-name(.)', 'VARCHAR(100)') Id,

a.c.value('@Number', 'INT') [Number],

a2.c2.value('@Name', 'VARCHAR(100)') Visitor,

a2.c2.value('@Location', 'VARCHAR(100)') Location

FROM XmlTable AS X

CROSS APPLY X.Xmldocument.nodes('dev:Doc/dev:Functions/dev:A1') a(c)

CROSS APPLY a.c.nodes('dev:Visitors/dev:Visitor') a2(c2);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 4

OK, here's another option. This procedure is a LOT more complicated than using the OPENXML method but it parses any XML document into an actual table of nodes, nodetypes, nodenames, etc. Once all of the XML information has been put into a table it just takes a little imagination and ingenuity to run queries against it. I use it mostly so I can tear apart a complicated XML schema so I can see what I have to work with. My mind just likes tables better than raw XML. 😛

For the purposes of this procedure I commented out a couple of header rows in your XML just for my own convenience. Usually I have the procedure clean up such things when I have a known schema to work with. The necessary procedures and functions are attached below.

DECLARE

@strInput VARCHAR(MAX)

,@Delimiter1 CHAR(1)

,@Delimiter2 CHAR(1)

SET @Delimiter1 = '|'

SET @Delimiter2 = ','

IF OBJECT_ID('tempdb..#ParsedXMLTable') IS NOT NULL

DROP TABLE #ParsedXMLTable

CREATE TABLE #ParsedXMLTable (

[RowNum] INT NOT NULL,

[id] INT NULL,

[parentid] INT NULL,

[nodepath] NVARCHAR(4000) NULL,

[nodetype] NVARCHAR(50) NULL,

[nodename] NVARCHAR(50) NULL,

[property] NVARCHAR(50) NULL,

[value] NVARCHAR(50) NULL,

[nodecontents] NVARCHAR(100) NULL,

[XMLStatus] NVARCHAR(50) NULL,

PRIMARY KEY (RowNum))

SET @strInput =

N'<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Settings="Testing" Title="Ordering">

<!--dev:Base RevisionNumber="0" Localid="E43D7" />

<dev:Rev Time="2013-01-21T15:08:00">

<dev:Person Name="Me" Systemid="54654" />

</dev:Rev-->

<dev:Functions Id="A1">

<dev:A1 Number="1">

<dev:Codes>D</dev:Codes>

<dev:Required>true</dev:Required>

<dev:Informational>false</dev:Informational>

<dev:Visitors>

<dev:Visitor Name="Dev01" Location="STLRF">

<dev:Divisions>

<dev:Division Number="1" Name="TFR3" Usage="Monitor">

<dev:Description>Development Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="2" Name="DEF32" Usage="Monitor">

<dev:Description>Testing Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="3" Name="DEP13" Usage="None">

<dev:Description>Guided Fundamentals</dev:Description>

</dev:Division>

</dev:Divisions>

</dev:Visitor>

<dev:Visitor Name="Dev02" Location="STLRF">

<dev:Divisions>

<dev:Division Number="1" Name="TFR3" Usage="Monitor">

<dev:Description>Development Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="2" Name="DEF32" Usage="Monitor">

<dev:Description>Testing Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="3" Name="DEP13" Usage="None">

<dev:Description>Guided Fundamentals</dev:Description>

</dev:Division>

</dev:Divisions>

</dev:Visitor>

<dev:Visitor Name="Dev03" Location="FGRTY">

<dev:Divisions>

<dev:Division Number="1" Name="TFR3" Usage="Monitor">

<dev:Description>Development Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="2" Name="DEF32" Usage="Monitor">

<dev:Description>Testing Fundamentals</dev:Description>

</dev:Division>

<dev:Division Number="3" Name="DEP13" Usage="None">

<dev:Description>Guided Fundamentals</dev:Description>

</dev:Division>

</dev:Divisions>

</dev:Visitor>

</dev:Visitors>

</dev:A1>

</dev:Functions>

</dev:Doc>'

SET @strInput = REPLACE(@strInput,'dev:','')

--just to prove it's still a valid XML document

--SELECT CONVERT(XML,@strInput) AS XMLOutput

INSERT INTO #ParsedXMLTable

EXEC dbo.ParseXML_ByXMLInput @strInput,'Doc','/Functions',1,0

--it's been parsed so here's the table with the results

SELECT * FROM #ParsedXMLTable

Now that might be enough in some cases. But just to show how the data can be queried and pivoted here's an example of one way it can be done.

DECLARE

@strInput VARCHAR(MAX)

--this will create an array of the selected columns

SELECT

@strInput = COALESCE(@strInput+@Delimiter1,'')

+CAST(x1.tid AS VARCHAR(4))

+@Delimiter2+CAST(x1.pid AS VARCHAR(4))

+@Delimiter2+CAST(x1.ttn AS VARCHAR(50))

+@Delimiter2+CAST(x1.ttp AS VARCHAR(50))

+@Delimiter2+CAST(x1.ttv AS VARCHAR(50))

FROM

(

SELECT DISTINCT

tt.id AS tid

,tt.nodename AS ttn

,tt.property AS ttp

,tt.value AS ttv

,tt.parentid AS pid

FROM

#ParsedXMLTable AS tt

WHERE

1=1

AND (tt.nodetype = 'property')

) x1

--this creates a pivot table

SELECT

d.Item1 AS ItemID

,d.Item2 AS Category

,MAX(CASE WHEN d.Item2 = 'A1' THEN d.Item4 ELSE '' END) AS A1

,MAX(CASE WHEN d.Item2 = 'Division' AND d.Item3 = 'Name' THEN d.Item4

ELSE '' END) AS Name

,MAX(CASE WHEN d.Item2 = 'Division' AND d.Item3 = 'Usage' THEN d.Item4

ELSE '' END) AS Usage

,MAX(CASE WHEN d.Item2 = 'Visitor' AND d.Item3 = 'Name' THEN d.Item4

ELSE '' END) AS VisitorName

,MAX(CASE WHEN d.Item2 = 'Visitor' AND d.Item3 = 'Location' THEN d.Item4

ELSE '' END) AS LocationName

FROM

(

SELECT

ID = (MAX(CASE c.itemnumber WHEN 1 THEN c.item END))

,Item1 = (MAX(CASE c.itemnumber WHEN 2 THEN c.item END))

,Item2 = (MAX(CASE c.itemnumber WHEN 3 THEN c.item END))

,Item3 = (MAX(CASE c.itemnumber WHEN 4 THEN c.item END))

,Item4 = (MAX(CASE c.itemnumber WHEN 5 THEN c.item END))

FROM

(

SELECT

@strInput

) a (parameter)

CROSS APPLY

dbo.tvfDelimitedSplit(parameter,@Delimiter1) b

CROSS APPLY

dbo.tvfDelimitedSplit(item,@Delimiter2) c

GROUP BY

b.ItemNumber

) d

WHERE

d.Item2 <> 'Functions'

GROUP BY

d.Item2

,d.Item1

ORDER BY

d.Item2

The ouput from the above:

ItemIDCategoryA1NameUsageVisitorNameLocationName

4A11

14DivisionTFR3Monitor

19DivisionDEF32Monitor

24DivisionDEP13None

33DivisionTFR3Monitor

38DivisionDEF32Monitor

43DivisionDEP13None

52DivisionTFR3Monitor

57DivisionDEF32Monitor

62DivisionDEP13None

10VisitorDev01STLRF

29VisitorDev02STLRF

48VisitorDev03FGRTY

All procedures and functions are attached below.