Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

FOR XML EXPLICIT Query Problem? 1

Status
Not open for further replies.
peterlyttle

peterlyttle

Technical User
Nov 6, 2006
139
GB
Hi, I am having a problem with the following query in that it doesnt seem to be nesting correctly ie. it should be like -

<address>
<hostname>PC1</hostname>
<ip>10.10.10.10</ip>
<mac>00-16-35-62-B0-70</mac>
</address>

However all i am getting is <hostname>PC1</hostname><hostname>PC2</hostname><hostname>PC3</hostname>etc

Does anyone have any ideas which part I have gone wrong at? Also what would be the easiest way to automatically generate the xml file on a schedule?

SELECT
1 as TAG,
NULL as PARENT,

NULL as [address!1],
NULL as [hostname!2],
NULL as [hostname!2!os],
NULL as [hostname!2!servertype],
NULL as [ip!3],
NULL as [mac!4],
NULL as [server!5],
NULL as [user!6]


union all

SELECT

2 as TAG,
1 as PARENT,

CS.[PC_UserName],
CS.[PC_Name] as PC_NAME,
replace(OS.[Caption], 'Microsoft Windows XP Professional', 'WinXP'),
NULL,
NULL,
NULL,
NULL,
NULL

FROM Net AS NI
JOIN System AS CS
ON CS.AID = NI.AID
JOIN Operating AS OS
ON CS.AID = OS.AID
WHERE CS.[PC_Name] LIKE '%PC%'
AND NI.[IPAddr] != '0.0.0.0'
AND NI.[Description] NOT LIKE '%Wireless%'
AND NI.[Description] != 'WAN (PPP/SLIP) Interface'

union all

SELECT

3 as TAG,
1 as PARENT,

NULL,
NULL,
NULL,
NULL,
NI.[IPAddr],
NULL,
NULL,
NULL

FROM Net AS NI
JOIN System AS CS
ON CS.AID = NI.AID
JOIN Operating AS OS
ON CS.AID = OS.AID
WHERE CS.[PC_Name] LIKE '%PC%'
AND NI.[IPAddr] != '0.0.0.0'
AND NI.[Description] NOT LIKE '%Wireless%'
AND NI.[Description] != 'WAN (PPP/SLIP) Interface'

union all

SELECT

4 as TAG,
1 as PARENT,

NULL,
NULL,
NULL,
NULL,
NULL,
NI.[MacAddr],
NULL,
NULL

FROM Net AS NI
JOIN System AS CS
ON CS.AID = NI.AID
JOIN Operating AS OS
ON CS.AID = OS.AID
WHERE CS.[PC_Name] LIKE '%PC%'
AND NI.[IPAddr] != '0.0.0.0'
AND NI.[Description] NOT LIKE '%Wireless%'
AND NI.[Description] != 'WAN (PPP/SLIP) Interface'

union all

SELECT

5 as TAG,
1 as PARENT,

NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
CS.[PC_Domain],
NULL

FROM Net AS NI
JOIN System AS CS
ON CS.AID = NI.AID
JOIN Operating AS OS
ON CS.AID = OS.AID
WHERE CS.[PC_Name] LIKE '%PC%'
AND NI.[IPAddr] != '0.0.0.0'
AND NI.[Description] NOT LIKE '%Wireless%'
AND NI.[Description] != 'WAN (PPP/SLIP) Interface'

union all

SELECT

6 as TAG,
1 as PARENT,

NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
replace(CS.[PC_UserName], 'TEST\','')


FROM Net AS NI
JOIN System AS CS
ON CS.AID = NI.AID
JOIN Operating AS OS
ON CS.AID = OS.AID
WHERE CS.[PC_Name] LIKE '%PC%'
AND NI.[IPAddr] != '0.0.0.0'
AND NI.[Description] NOT LIKE '%Wireless%'
AND NI.[Description] != 'WAN (PPP/SLIP) Interface'

FOR XML EXPLICIT
 
Sort by date Sort by votes
I think your problem is here:

[tt][blue]
SELECT
1 as TAG,
NULL as PARENT,

NULL as [address!1],
NULL as [hostname![!]2[/!]],
NULL as [hostname![!]2[/!]!os],
NULL as [hostname![!]2[/!]!servertype],
NULL as [ip![!]3[/!]],
NULL as [mac![!]4[/!]],
NULL as [server![!]5[/!]],
NULL as [user![!]6[/!]]
[/blue][/tt]

The number you see highlighted in red correspond to the nesting level. In your example xml, you only show 2 nesting level, so... I recommend changing the highlighted numbers to 2 for all of them.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Hi, this is what I thought when I first tried it, however when i change the numbers in red to "2" i get the following message -

XML tag ID 2 that was originally declared as 'hostname' is being redeclared as 'ip'.

This is why I thought the tag's needed to be different numbers.
 
Upvote 0 Downvote
I'm working up an example now. I'll post in a couple minutes.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
XML with sql server 2000 is a messy business. I did work up this example which seems to give you the structure you want. Of course I am hard coding data, but that also means you can copy/paste to query analyzer and play around with it for a bit before implementing it in your code.

Code: 
SELECT
    1 as TAG,
    NULL as PARENT,

    NULL as [address!1],
    NULL as [address!1!hostname!element],
    NULL as [address!1!os!element],
    NULL as [address!1!servertype!element],
    NULL as [address!1!ip!element],
    NULL as [address!1!mac!element],
    NULL as [address!1!server!element],
    NULL as [address!1!user!element]

Union All

Select 1, NULL, NULL, 'PC1', 'Blah', 'blah', '10.10.10.10', '00-00-00-00-00-00', 'server', 'user'
Union All
Select 1, NULL, NULL, 'PC2', 'windows', 'windows nt 4.0', '10.10.10.11', '11-11-11-11-11-11', 'The server', 'some user'

For xml explicit

When you run this code, you should notice that it is creating XML in the stucture you show in your original question. I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Hi, thanks for the example, however when i run this i get the following returned -

<address/><address><hostname>PC1</hostname><os>Blah</os><servertype>blah</servertype><ip>10.10.10.10</ip><mac>00-00-00-00-00-00</mac><server>server</server><user>user</user></address><address><hostname>PC2</hostname><os>windows</os><servertype>windows nt 4

Would there be any reason for it closing a tag at the beginning or not closing the last 2 tags?

Thanks for the help.
 
Upvote 0 Downvote
The first one is closing because we only have 1 root element now (address) and the first insert statement has all data as NULL so it's an empty element.

You are not seeing the closing tags because query analyzer is chopping off your data. Click Tools -> Options. Click 'Results' tab. Change the 'Maximum Chaacters Per Column' to something larger (I have mine set at 8000).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Is there any way around it closing the tag for NULL values?
That Max Characters Per Column did the trick nicely.
 
Upvote 0 Downvote
Well, 1 way comes to mind. Don't select records where every column is null. Remember, this is just an example. When you implement this in your query, just be careful about what you select.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
You could also replace NULLs with empty strings. Of course all the data will be empty, but if that's what you want...

Code: 
SELECT
    1 as TAG,
    NULL as PARENT,

    '' as [address!1],
    '' as [address!1!hostname!element],
    '' as [address!1!os!element],
    '' as [address!1!servertype!element],
    '' as [address!1!ip!element],
    '' as [address!1!mac!element],
    '' as [address!1!server!element],
    '' as [address!1!user!element]

Union All

Select 1, NULL, NULL, 'PC1', 'Blah', 'blah', '10.10.10.10', '00-00-00-00-00-00', 'server', 'user'
Union All
Select 1, NULL, NULL, 'PC2', 'windows', 'windows nt 4.0', '10.10.10.11', '11-11-11-11-11-11', 'The server', 'some user'

For xml explicit

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Ah right, the reason I need null strings is for the program that is opening the xml file. Below is the exact format I am trying to replicate, also I had missed out the top level addrlist which xml needs -

<addrlist version="4">
<address>
<hostname>PC1</hostname>
<ip>10.0.0.1</ip>
<mac>00:00:00:00:00:00</mac>
<server>Domain1</server>
<user>User1</user>
</address>
<address>
<hostname>PC2</hostname>
<ip>10.0.0.2</ip>
<mac>00:00:00:00:00:00</mac>
<server>Domain1</server>
<user>User2</user>
</address>
</addrlist>

Thanks again for the help
 
Upvote 0 Downvote
It's a little unclear to me... Are you ok now, or do you still need more assistance?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Hi, im affraid im still stuck. I dont mind some blank values as long as the tags are opened / closed in the correct order, so the

SELECT
1 as TAG,
NULL as PARENT,

'' as [address!1],
'' as [address!1!hostname!element],
'' as [address!1!os!element],
'' as [address!1!servertype!element],
'' as [address!1!ip!element],
'' as [address!1!mac!element],
'' as [address!1!server!element],
'' as [address!1!user!element]

Union All

Select 1, NULL, NULL, 'PC1', 'Blah', 'blah', '10.10.10.10', '00-00-00-00-00-00', 'server', 'user'
Union All
Select 1, NULL, NULL, 'PC2', 'windows', 'windows nt 4.0', '10.10.10.11', '11-11-11-11-11-11', 'The server', 'some user'

For xml explicit

The above is the best as I can remove the 1st <address> as it will be blank rather than unopened tags. However as I need an <addrlist> at the very beginning / end I am unsure how to go about this.

I have tried changing [address!1!os!element] to [addrlist!1!address!os!element] and the other values the same but this does not work as I expected.
 
Upvote 0 Downvote
In that case...

Code: 
SELECT
    1 as TAG,
    NULL as PARENT,

    '' as [!][addrlist!1][/!],
    '' as [address!2!hostname!element],
    '' as [address!2!os!element],
    '' as [address!2!servertype!element],
    '' as [address!2!ip!element],
    '' as [address!2!mac!element],
    '' as [address!2!server!element],
    '' as [address!2!user!element]

Union All

Select [!]2, 1[/!], NULL, 'PC1', 'Blah', 'blah', '10.10.10.10', '00-00-00-00-00-00', 'server', 'user'
Union All
Select [!]2, 1[/!], NULL, 'PC2', 'windows', 'windows nt 4.0', '10.10.10.11', '11-11-11-11-11-11', 'The server', 'some user'

For xml explicit

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
ahh right I hadnt been doing the 2,1 on the select's I had been doing just 2, - Cheers this is exactly what im after :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Question
Parameterised sql server query
Replies
8
Views
401
Chriss Miller
C
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
833
Andrzejek
Andrzejek
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
591
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
Adding Text to and XML Field 1
Replies
2
Views
319
grnzbra
grnzbra
ElEye
  • Locked
  • Question
Parse XML via SQL query 1
Replies
1
Views
296
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top